Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
talbrecht
Posts: 10
Joined: Wed 26 Oct 2016 14:06

Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Post by talbrecht » 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!

Code: Select all

        var query = dbContext.Foos.Include(nameof(Foo.Bars))
          .OrderByDescending(x => x.Value)
          .Skip(5)
          .Take(5);
        var sql = query.ToString();
generates
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();
The correct SQL should be:
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
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.

Please priorize this issue for getting fixed! My current work is blocked by it. :(

Best regards
Thomas
Last edited by talbrecht on Tue 21 Mar 2017 22:45, edited 1 time in total.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Post by Shalex » Tue 21 Mar 2017 18:50

Thank you for your report. We will notify you when the issue is fixed.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Post by Shalex » Wed 22 Mar 2017 16:25

The bug with ordering within subqueries in EF1, EF4, EF5, EF6 is fixed. We have just sent an internal build with the fix to your email.

talbrecht
Posts: 10
Joined: Wed 26 Oct 2016 14:06

Re: Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Post by talbrecht » Thu 23 Mar 2017 11:02

Hi Devart team!

Many thanks for this prompt fix. I can confirm that the issue is resolved with the new build.

Best regards,
Thomas

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Post by Shalex » Thu 23 Mar 2017 18:36

Thank you for confirming fix.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL if EF Include() with OrderBy(), Skip() and Take() is used

Post by Shalex » Fri 26 May 2017 18:11

The bug with ordering within subqueries in EF1, EF4, EF5, EF6 is fixed in a public build: viewtopic.php?f=29&t=35439.

Post Reply