PETAPOCO - Invalid object name

Question

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?

Answer 1

Remove the whitespace before the semi-colon.

According to Alex Jorgenson's link, this is fixed if you make a semi-colon the very first character. According to my testing, this is strictly the first character, i.e. if there is even some whitespace before the semi-colon, the auto-generated code will still be spit out.

Answer 2

This is a known issue with Peta Poco. It appears to be fixed in some version but I don't know which one. You can find more information and a work around for this particular issue at https://github.com/toptensoftware/PetaPoco/issues/22