Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used
Posted: Mon 20 Mar 2017 11:24
Hi,
I've found a serious bug in the SQL generation of dotConnect for SQLite. I've reproduced it in v5.7.837, v5.7.852 and v5.8.862. Please provide a bugfix release as soon as possible!
generates
The ORDER BY clause is not applied before LIMIT and OFFSET. The generated SQL statement represents the following LINQ statement instead:
The correct SQL should be:
Please priorize this issue for getting fixed! My current work is blocked by it.
Best regards
Thomas
I've found a serious bug in the SQL generation of dotConnect for SQLite. I've reproduced it in v5.7.837, v5.7.852 and v5.8.862. Please provide a bugfix release as soon as possible!
Code: Select all
var query = dbContext.Foos.Include(nameof(Foo.Bars))
.OrderByDescending(x => x.Value)
.Skip(5)
.Take(5);
var sql = query.ToString();
SELECT
Project1.Value,
Project1.Id,
Project1.C1,
Project1.Id1,
Project1.Name,
Project1.Foo_Id
FROM ( SELECT
Limit1.Id,
Limit1.Value,
Extent2.Id AS Id1,
Extent2.Name,
Extent2.Foo_Id,
CASE WHEN Extent2.Id IS NULL THEN CAST(NULL AS int32) ELSE 1 END AS C1
FROM (SELECT
Extent1.Id,
Extent1.Value
FROM Foos AS Extent1
LIMIT 5 OFFSET 5 ) AS Limit1
LEFT OUTER JOIN Bars AS Extent2 ON Limit1.Id = Extent2.Foo_Id
) AS Project1
ORDER BY Project1.Value DESC, Project1.Id ASC, Project1.C1 ASC
The ORDER BY clause is not applied before LIMIT and OFFSET. The generated SQL statement represents the following LINQ statement instead:Code: Select all
var query = dbContext.Foos.Include(nameof(Foo.Bars))
.Skip(5)
.Take(5)
.OrderByDescending(x => x.Value);
var sql = query.ToString();
I can provide a demo project to reproduce this issue - although the issue is quite obvious and easy reproducible. Also I can create a support request via customer portal if required.SELECT
Project1.Value,
Project1.Id,
Project1.C1,
Project1.Id1,
Project1.Name,
Project1.Foo_Id
FROM ( SELECT
Limit1.Id,
Limit1.Value,
Extent2.Id AS Id1,
Extent2.Name,
Extent2.Foo_Id,
CASE WHEN Extent2.Id IS NULL THEN CAST(NULL AS int32) ELSE 1 END AS C1
FROM (SELECT
Extent1.Id,
Extent1.Value
FROM Foos AS Extent1
ORDER BY Extent1.Value DESC
LIMIT 5 OFFSET 5 ) AS Limit1
LEFT OUTER JOIN Bars AS Extent2 ON Limit1.Id = Extent2.Foo_Id
) AS Project1
ORDER BY Project1.Value DESC, Project1.Id ASC, Project1.C1 ASC
Please priorize this issue for getting fixed! My current work is blocked by it.
Best regards
Thomas