Sort problem with eager loading and skip and take

JeroenF · Post by **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?

AndreyR · Post by **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 · Post by **AndreyR** » Fri 10 Jun 2011 11:36

The problem is fixed, the new build will be available in a week or so.

AndreyR · Post by **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.