Having a very difficult time determining the root cause of this bug, not sure if its a devart connector issue or what.
I have two queries that are simply generating a list of data and outputting in a table. When the data is outputted in a table you can page through it. Loading the first page (the first query below) successfully works. Loading the second page throws an exception (the second query) - the exception is listed below. If I take these queries out of the system using the IQueryable ToQueryString ext and run them manually in mysql they both work. I'm at a loss what to test/do next.
The only difference between the two queries is the LIMIT OFFSET values.
Here are the first query that succeeds in .net code and succeeds when running directly as MySql query:
SELECT d0.City, d0.County, t.DiggerSearchId, t.DiggerSearchRequestId, t.DiggerSearchRequestJurisdictionId, d0.SearchType, d1.Debug, d2.ServiceCategory, d0.CourtType, d1.DOB, t.FailureCount AS FailCount, d1.FirstName, d1.LastName, d0.State, t.SentDateTime AS LastAttempt, d3.ActualResult, d3.ListResult, d3.ParserResult, d3.SearchResult
FROM (
SELECT d.DiggerSearchRequestJurisdictionId, d.DiggerSearchId, d.DiggerSearchRequestId, d.FailureCount, d.SentDateTime
FROM digger_work_queue AS d
WHERE d.Status = 0
LIMIT 10 OFFSET 0
) AS t
INNER JOIN digger_search AS d0 ON t.DiggerSearchId = d0.DiggerSearchId
INNER JOIN digger_search_requests AS d1 ON t.DiggerSearchRequestId = d1.DiggerSearchRequestId
LEFT JOIN digger_package AS d2 ON d1.DiggerPackageId = d2.DiggerPackageId
INNER JOIN digger_search_request_jurisdictions AS d3 ON t.DiggerSearchRequestJurisdictionId = d3.DiggerSearchRequestJurisdictionId
Here is the second query that fails when running in .net code but succeeds when running directly as MySql query:
SELECT d0.City, d0.County, t.DiggerSearchId, t.DiggerSearchRequestId, t.DiggerSearchRequestJurisdictionId, d0.SearchType, d1.Debug, d2.ServiceCategory, d0.CourtType, d1.DOB, t.FailureCount AS FailCount, d1.FirstName, d1.LastName, d0.State, t.SentDateTime AS LastAttempt, d3.ActualResult, d3.ListResult, d3.ParserResult, d3.SearchResult
FROM (
SELECT d.DiggerSearchRequestJurisdictionId, d.DiggerSearchId, d.DiggerSearchRequestId, d.FailureCount, d.SentDateTime
FROM digger_work_queue AS d
WHERE d.Status = 0
LIMIT 10 OFFSET 10
) AS t
INNER JOIN digger_search AS d0 ON t.DiggerSearchId = d0.DiggerSearchId
INNER JOIN digger_search_requests AS d1 ON t.DiggerSearchRequestId = d1.DiggerSearchRequestId
LEFT JOIN digger_package AS d2 ON d1.DiggerPackageId = d2.DiggerPackageId
INNER JOIN digger_search_request_jurisdictions AS d3 ON t.DiggerSearchRequestJurisdictionId = d3.DiggerSearchRequestJurisdictionId
Here is the exception that occurs when the query executes in .net code:
Devart.Data.MySql.MySqlException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':10 ) AS t INNER JOIN digger_search AS d0 ON t.DiggerSearchId = d0.DiggerSearc' at line 6'
I'm not sure if it is something to do with how the connector swaps out variables in the query itself before executing or what.
Here is our ToPagedList extension method:
public static async Task<PagedList<T>> ToPagedListAsync<T>(this IQueryable<T> query, int pageNumber = 1, int totalRowsPerPage = 10) where T : class
{
// Initialize a new instance of PagedList<T>
var result = new PagedList<T>
{
// If pageNumber is not equal to 0, set CurrentPage to pageNumber; otherwise, set it to 1.
CurrentPage = pageNumber != 0 ? pageNumber : 1,
// If totalRowsPerPage is not equal to 0, set TotalRowsPerPage to totalRowsPerPage;
// otherwise, set it to 10.
TotalRowsPerPage = totalRowsPerPage != 0 ? totalRowsPerPage : 10,
// Set TotalRows to the count of items in the query.
TotalRows = query.Count()
};
// If totalRowsPerPage is not equal to -1 (indicating no paging), calculate paging
// information and retrieve the items for the current page.
if (totalRowsPerPage != -1)
{
// Calculate the total number of pages.
var totalPages = (double)result.TotalRows / result.TotalRowsPerPage;
result.TotalPages = (int)Math.Ceiling(totalPages);
// Calculate the number of items to skip and retrieve the items for the current page.
var skip = (result.CurrentPage - 1) * result.TotalRowsPerPage;
result.Items = await query.Skip(skip).Take(result.TotalRowsPerPage).ToListAsync();
}
else
{
// If totalRowsPerPage is -1, retrieve all items in the query.
result.Items = await query.ToListAsync();
}
// Return the paged list.
return result;
}
