Sort problem with eager loading and skip and take

Sort problem with eager loading and skip and take

Postby JeroenF » Fri 15 Apr 2011 11:42

We got an entity called Relation which has a collection of Address. In our web page we want to show the data of the first Address of a Relation. Because we don't want 1+n queries, therefor we use eager loading.

Our query in EF4 is as follows:

entities.Relations.Include("Addresses").OrderBy(b => b.Name).Skip(50).Take(50);

The query created by DevArt incorrectly first skips and takes the Relations, then joins that result with the Address table and then Sorts that result by the Relation.Name. This is incorrect, because it should first sort the relations by name, then skip 50 and take the next 50 and then join the Address table.

The query generated by Devart is as follows:
Code: Select all
SELECT
"Project2"."Id" AS "Id",
"Project2"."C1" AS "C1",
"Project2"."Name" AS "Name",
"Project2"."Created" AS "Created",
"Project2"."LastModified" AS "LastModified",
"Project2"."Blocked" AS "Blocked",
"Project2"."Deleted" AS "Deleted",
"Project2"."CreatedBy_Id" AS "CreatedBy_Id",
"Project2"."LastModifiedBy_Id" AS "LastModifiedBy_Id",
"Project2"."C2" AS "C2",
"Project2"."Id1" AS "Id1",
"Project2"."Street" AS "Street",
"Project2"."PostalCode" AS "PostalCode",
"Project2"."City" AS "City",
"Project2"."Created1" AS "Created1",
"Project2"."LastModified1" AS "LastModified1",
"Project2"."RelationId" AS "RelationId",
"Project2"."CreatedBy_Id1" AS "CreatedBy_Id1",
"Project2"."LastModifiedBy_Id1" AS "LastModifiedBy_Id1"
FROM ( SELECT
   "Limit1"."Id" AS "Id",
   "Limit1"."Name" AS "Name",
   "Limit1"."Created" AS "Created",
   "Limit1"."LastModified" AS "LastModified",
   "Limit1"."Blocked" AS "Blocked",
   "Limit1"."Deleted" AS "Deleted",
   "Limit1"."CreatedBy_Id" AS "CreatedBy_Id",
   "Limit1"."LastModifiedBy_Id" AS "LastModifiedBy_Id",
   "Limit1"."C1" AS "C1",
   "Extent2"."Id" AS "Id1",
   "Extent2"."Street" AS "Street",
   "Extent2"."PostalCode" AS "PostalCode",
   "Extent2"."City" AS "City",
   "Extent2"."Created" AS "Created1",
   "Extent2"."LastModified" AS "LastModified1",
   "Extent2"."RelationId" AS "RelationId",
   "Extent2"."CreatedBy_Id" AS "CreatedBy_Id1",
   "Extent2"."LastModifiedBy_Id" AS "LastModifiedBy_Id1",
   CASE WHEN "Extent2"."Id" IS NULL THEN  CAST(NULL AS int) ELSE 1 END AS "C2"
   FROM   (SELECT "Project1"."Id" AS "Id", "Project1"."Name" AS "Name", "Project1"."Created" AS "Created", "Project1"."LastModified" AS "LastModified", "Project1"."Blocked" AS "Blocked", "Project1"."Deleted" AS "Deleted", "Project1"."CreatedBy_Id" AS "CreatedBy_Id", "Project1"."LastModifiedBy_Id" AS "LastModifiedBy_Id", "Project1"."C1" AS "C1"
      FROM ( SELECT
         "Extent1"."Id" AS "Id",
         "Extent1"."Name" AS "Name",
         "Extent1"."Created" AS "Created",
         "Extent1"."LastModified" AS "LastModified",
         "Extent1"."Blocked" AS "Blocked",
         "Extent1"."Deleted" AS "Deleted",
         "Extent1"."CreatedBy_Id" AS "CreatedBy_Id",
         "Extent1"."LastModifiedBy_Id" AS "LastModifiedBy_Id",
         1 AS "C1"
         FROM public."Relations" AS "Extent1"
      )  AS "Project1"
      LIMIT 50 OFFSET 50  ) AS "Limit1"
   LEFT OUTER JOIN public."Addresses" AS "Extent2" ON "Limit1"."Id" = "Extent2"."RelationId"
)  AS "Project2"
ORDER BY "Project2"."Name" ASC, "Project2"."Id" ASC, "Project2"."C2" ASC


If we use SQLServer as our database and therefor the standard Linq to Entities generator the following query is created:
Code: Select all
SELECT
[Project2].[Id] AS [Id],
[Project2].[C1] AS [C1],
[Project2].[Name] AS [Name],
[Project2].[Created] AS [Created],
[Project2].[LastModified] AS [LastModified],
[Project2].[Blocked] AS [Blocked],
[Project2].[Deleted] AS [Deleted],
[Project2].[CreatedBy_Id] AS [CreatedBy_Id],
[Project2].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
[Project2].[C2] AS [C2],
[Project2].[Id1] AS [Id1],
[Project2].[Street] AS [Street],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[City] AS [City],
[Project2].[Created1] AS [Created1],
[Project2].[LastModified1] AS [LastModified1],
[Project2].[RelationId] AS [RelationId],
[Project2].[CreatedBy_Id1] AS [CreatedBy_Id1],
[Project2].[LastModifiedBy_Id1] AS [LastModifiedBy_Id1]
FROM ( SELECT
   [Limit1].[Id] AS [Id],
   [Limit1].[Name] AS [Name],
   [Limit1].[Created] AS [Created],
   [Limit1].[LastModified] AS [LastModified],
   [Limit1].[Blocked] AS [Blocked],
   [Limit1].[Deleted] AS [Deleted],
   [Limit1].[CreatedBy_Id] AS [CreatedBy_Id],
   [Limit1].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
   [Limit1].[C1] AS [C1],
   [Extent2].[Id] AS [Id1],
   [Extent2].[Street] AS [Street],
   [Extent2].[PostalCode] AS [PostalCode],
   [Extent2].[City] AS [City],
   [Extent2].[Created] AS [Created1],
   [Extent2].[LastModified] AS [LastModified1],
   [Extent2].[RelationId] AS [RelationId],
   [Extent2].[CreatedBy_Id] AS [CreatedBy_Id1],
   [Extent2].[LastModifiedBy_Id] AS [LastModifiedBy_Id1],
   CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
   FROM   (SELECT TOP (10) [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[Created] AS [Created], [Project1].[LastModified] AS [LastModified], [Project1].[Blocked] AS [Blocked], [Project1].[Deleted] AS [Deleted], [Project1].[CreatedBy_Id] AS [CreatedBy_Id], [Project1].[LastModifiedBy_Id] AS [LastModifiedBy_Id], [Project1].[C1] AS [C1]
      FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[Created] AS [Created], [Project1].[LastModified] AS [LastModified], [Project1].[Blocked] AS [Blocked], [Project1].[Deleted] AS [Deleted], [Project1].[CreatedBy_Id] AS [CreatedBy_Id], [Project1].[LastModifiedBy_Id] AS [LastModifiedBy_Id], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[Name] ASC, [Project1].[Id] ASC) AS [row_number]
         FROM ( SELECT
            [Extent1].[Id] AS [Id],
            [Extent1].[Name] AS [Name],
            [Extent1].[Created] AS [Created],
            [Extent1].[LastModified] AS [LastModified],
            [Extent1].[Blocked] AS [Blocked],
            [Extent1].[Deleted] AS [Deleted],
            [Extent1].[CreatedBy_Id] AS [CreatedBy_Id],
            [Extent1].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
            1 AS [C1]
            FROM [dbo].[Relations] AS [Extent1]
         )  AS [Project1]
      )  AS [Project1]
      WHERE [Project1].[row_number] > 10
      ORDER BY [Project1].[Name] ASC, [Project1].[Id] ASC ) AS [Limit1]
   LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2] ON [Limit1].[Id] = [Extent2].[RelationId]
)  AS [Project2]
ORDER BY [Project2].[Name] ASC, [Project2].[Id] ASC, [Project2].[C2] ASC


Is there a way to tell the Devart postgresql code generator to not put the sort statement on the outside statement?
JeroenF
 
Posts: 5
Joined: Thu 14 Apr 2011 14:38

Postby AndreyR » Tue 19 Apr 2011 11:41

Thank you for the report, we have reproduced the error.
I will let you know about the results of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Fri 10 Jun 2011 11:36

The problem is fixed, the new build will be available in a week or so.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Thu 16 Jun 2011 11:22

The new fixed 5.30.172 build of dotConnect for PostgreSQL can be dowloaded from here (the trial version) or from Registered Users' Area (for users with active subscription only):
For the detailed information about the improvements and fixes available in dotConnect for PostgreSQL 5.30.172, please refer to this announcement.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL