I am using CTE with PetaPOCO and getting a strange error
SQL Exception: Invalid Object Name PayTransactionForRollingVacationAverage that references the model that the data should map to.
The code is as follows.
public IEnumerable<PayTransactionForRollingVacationAverage> GetPayTransactionForRollingVacationAverage(DateTime payEndingDate)
{
PointsNorth.PetaPoco.Sql sql = new PointsNorth.PetaPoco.Sql();
sql.Append(@"
;with HolidayWeeks as
(
Select Distinct EmployeeId, PayEndDate, 'Y' as HolidayWeek
from PayTransactions
where PayEndDate = @payEndingDate
and LaborCode in ('251', '249')
)", new { payEndingDate });
sql.Append(@"
Select
PT.EmployeeId,
PT.PayEndDate,
J.JobClass,
PayCodes.AverageRateCode,
PT.RegularHours,
PT.RegularRate,
PT.RegularAmount
from PayTransactions PT
Left Outer Join PayCodes on PayCodes.PayCodeCode = PT.LaborCode
Left Outer Join HolidayWeeks as H on H.PayEndDate = PT.PayEndDate and H.EmployeeId = PT.EmployeeId
Inner Join Jobs as J on J.JobId = PT.JobId
where PT.PayEndDate = @payEndingDate
and IsNull(H.HolidayWeek, 'N') <> 'Y'
order by PT.EmployeeId, PT.PayEndDate, J.JobClass", new { payEndingDate });
var data = Database.Query<PayTransactionForRollingVacationAverage>(sql);
return data;
}
The model is pretty simple:
public class PayTransactionForRollingVacationAverage
{
public long EmployeeId { get; set; }
public DateTime PayEndDate { get; set; }
public string JobClass { get; set; }
public string AverageRateCode { get; set; }
public decimal RegularHours { get; set; }
public decimal RegularRate { get; set; }
public decimal RegularAmount { get; set; }
}
I tried breaking the SQL up to make sure it was building correctly, but I still get the error. Any idea why this is occurring?