SCM

[#1011208] Wrong SQL statement based on LINQ Query

View Trackers | Bugs | Download .csv | Monitor

Date:
2012-06-25 13:34
Priority:
3
State:
Open
Submitted by:
Marc Müller (marcmueller)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
None
Summary:
Wrong SQL statement based on LINQ Query

Detailed description
Hello everybody

We are using the current version of npgsql in our project. Currently we are facing an error when querying the database with a specific statement. The error occurs if we are doing the following:
- Query an entity with eager loading the related entities by using include()
- sorting the IQueryable at the end
- use Take() and Skip() for paging.

In this case, the generated SQL contains a syntax error.

Error description:

LINQ statement:
var pers = ctx.Get<PEntity>()
.Include(c => c.R1)
.Include(p => p.R2)
.Include(c => c.R3)
.Include(o => o.R4);
pers = pers.OrderBy(k => k.EntityId).Skip(5).Take(10);
var results = pers.ToList();

The generated SQL statement contains the ORDER BY as well as the OFFSET and LIMIT statement for each subquery instead beeing added once at the end.

Simplyfied generated SQL statement:

SELECT "UnionAll2"."C2" AS ...
FROM (SELECT "UnionAll1"."C1" AS "C1...
FROM ( SELECT
CASE WHEN ("Extent3"."Pers++Roles+id" IS NULL )
THEN (CAST (NULL AS int4))
ELSE (1) END
AS "C1",
"Project1"."Pers+id" AS "Pers+id","Project1"."C1" AS "C2","Project1"."Pers+id" ...
FROM (SELECT "Extent1"."Biz+AddressLine" AS "Biz+AddressLine","Extent1"."Biz+AddressLineOther" ..
LEFT OUTER JOIN "public"."Pos" AS "Extent2" ON "Extent1"."StafVac+PtrLastConnectedEntityID"="Extent2"."Pos+id") AS "Project1"
ORDER BY "Project1"."Pers+id" ASC
OFFSET 5
LIMIT 10

LEFT OUTER JOIN "public"."Pers++Roles" AS "Extent3" ON "Project1"."Pers+id"="Extent3"."Pers+id"
UNION ALL

SELECT 2 AS "C1","Project3"."Pers+id" ...
LEFT OUTER JOIN "public"."Pos" AS "Extent5" ON "Extent4"."StafVac+PtrLastConnectedEntityID"="Extent5"."Pos+id") AS "Project3"
ORDER BY "Project3"."Pers+id" ASC
OFFSET 5
LIMIT 10

INNER JOIN "public"."Pers++PhotoF" AS "Extent6" ON "Project3"."Pers+id"="Extent6"."Pers+id") AS "UnionAll1" UNION ALL SELECT 3 AS "C1","Project6"."Pers+id" AS "Pers+id ...

ORDER BY "Project6"."Pers+id" ASC
OFFSET 5
LIMIT 10

INNER JOIN "public"."WhoProfiles" AS "Extent9" ON "Project6"."Pers+id"="Extent9"."PtrPersonID")
AS "UnionAll2"
ORDER BY "UnionAll2"."C2" ASC ,"UnionAll2"."C111" ASC ,"UnionAll2"."C1" ASC

The section with the ORDER BY, OFFSET and LIMIT should only be applied once at the end of the query. Why does this happend? Is it possible to fix this issue because this is a blocking issue in our project.

Thank you in advance for your answer!

Cheers
Marc

Followup

Message
Date: 2012-06-30 02:57
Sender: Josh Cooley

The orderby, offset, and limit belong in the inner part of the query because they apply the the main entity and not the joined entities. You are limiting it to 10 of the PEntity type, but you may get 10000 of the related entities.

This still has a bug in it though. Too much has been stripped away in trying to build the aliases correctly. These inner queries need to be wrapped in parenthesis and aliased. I'm working on getting the syntax right before I can start on the code change.

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge