SCM

[#1010938] Invalid Sql Query Generation when Using "Include" (Join) in Entity Framework 4

View Trackers | Bugs | Download .csv | Monitor

Date:
2010-10-28 09:33
Priority:
3
State:
Open
Submitted by:
Constantinus van der Kruijs (kruijs)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
None
Summary:
Invalid Sql Query Generation when Using "Include" (Join) in Entity Framework 4

Detailed description
Hi,

I'm currently using the Npqsql library (2.0.10.0) with EF 4, and want to intially load reference properties on entites using a query like this:

(ObjectContext).Users.OfType<Person>().Where(u => u.LoginName == loginName).Skip(0).Take(15).Include("PhotoFiles")

In this case, the query is executed successfully. When using multiple Include calls, though, the generated sql query is erroreous:

(ObjectContext).Users.OfType<Person>().Where(u => u.LoginName == loginName).Skip(0).Take(15).Include("PhotoFiles").Include("Custodian");

The query than looks something like this:

SELECT "Project2"."Pers+id" AS "Pers+id","Project2"."C2" AS "C1","Project2"."C1" AS "C2","Project2"."User+LoginName" AS "User+LoginName","Project2"."PhotoF+Name" AS "PhotoF+Name","Project2"."PhotoF+Size" AS "PhotoF+Size","Project2"."PhotoF+Type" AS "PhotoF+Type","Project2"."EntResp+PtrCustodianID" AS "EntResp+PtrCustodianID","Project2"."C3" AS "C3","Project2"."Pers+id1" AS "Pers+id1","Project2"."User+LoginName1" AS "User+LoginName1","Project2"."C4" AS "C4","Project2"."PhotoF+Name1" AS "PhotoF+Name1","Project2"."PhotoF+Size1" AS "PhotoF+Size1","Project2"."PhotoF+Type1" AS "PhotoF+Type1","Project2"."EntResp+PtrCustodianID1" AS "EntResp+PtrCustodianID1","Project2"."C5" AS "C5","Project2"."Pers++PhotoF+id" AS "Pers++PhotoF+id","Project2"."Pers+id2" AS "Pers+id2","Project2"."SpecialName" AS "SpecialName" FROM (SELECT "Project1"."Pers+id" AS "Pers+id","Project1"."EntResp+PtrCustodianID" AS "EntResp+PtrCustodianID","Project1"."PhotoF+Name" AS "PhotoF+Name","Project1"."PhotoF+Size" AS "PhotoF+Size","Project1"."PhotoF+Type" AS "PhotoF+Type","Project1"."User+LoginName" AS "User+LoginName","Project1"."C1" AS "C1","Project1"."C2" AS "C2","Project1"."Pers+id1" AS "Pers+id1","Project1"."EntResp+PtrCustodianID1" AS "EntResp+PtrCustodianID1","Project1"."PhotoF+Name1" AS "PhotoF+Name1","Project1"."PhotoF+Size1" AS "PhotoF+Size1","Project1"."PhotoF+Type1" AS "PhotoF+Type1","Project1"."User+LoginName1" AS "User+LoginName1","Project1"."C3" AS "C3","Project1"."C4" AS "C4","Extent3"."Pers++PhotoF+id" AS "Pers++PhotoF+id","Extent3"."Pers+id" AS "Pers+id2","Extent3"."SpecialName" AS "SpecialName", CASE WHEN ("Extent3"."Pers++PhotoF+id" IS NULL ) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C5" FROM (SELECT "Extent1"."Pers+id" AS "Pers+id","Extent1"."EntResp+PtrCustodianID" AS "EntResp+PtrCustodianID","Extent1"."PhotoF+Name" AS "PhotoF+Name","Extent1"."PhotoF+Size" AS "PhotoF+Size","Extent1"."PhotoF+Type" AS "PhotoF+Type","Extent1"."User+LoginName" AS "User+LoginName", CASE WHEN ("Extent1"."EntitySubtype"='PersonType_Applicant') THEN ('0X0X0X') ELSE ('0X0X0X0X') END AS "C1",1 AS "C2","Extent2"."Pers+id" AS "Pers+id1","Extent2"."EntResp+PtrCustodianID" AS "EntResp+PtrCustodianID1","Extent2"."PhotoF+Name" AS "PhotoF+Name1","Extent2"."PhotoF+Size" AS "PhotoF+Size1","Extent2"."PhotoF+Type" AS "PhotoF+Type1","Extent2"."User+LoginName" AS "User+LoginName1", CASE WHEN ("Extent2"."Pers+id" IS NULL ) THEN (CAST (NULL AS varchar)) WHEN ("Extent2"."EntitySubtype"='PersonType_User') THEN ('0X0X') WHEN ("Extent2"."EntitySubtype"='PersonType_Applicant') THEN ('0X0X0X') WHEN ("Extent2"."EntitySubtype"='PersonType_Visitors') THEN ('0X0X1X') WHEN ("Extent2"."EntitySubtype"='PersonType_SysAdmin') THEN ('0X0X2X') WHEN ("Extent2"."EntitySubtype"='PersonType_Evaluator') THEN ('0X0X3X') WHEN ("Extent2"."EntitySubtype"='PersonType_Deputy') THEN ('0X0X4X') ELSE ('0X0X0X0X') END AS "C3", CASE WHEN ("Extent2"."Pers+id" IS NULL ) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C4" FROM "public"."Pers" AS "Extent1" LEFT OUTER JOIN "public"."Pers" AS "Extent2" ON (((("Extent2"."EntitySubtype"='PersonType_User') OR ("Extent2"."EntitySubtype"='PersonType_Applicant')) OR (("Extent2"."EntitySubtype"='PersonType_Visitors') OR ("Extent2"."EntitySubtype"='PersonType_SysAdmin'))) OR ((("Extent2"."EntitySubtype"='PersonType_Evaluator') OR ("Extent2"."EntitySubtype"='PersonType_Deputy')) OR ("Extent2"."EntitySubtype"='PersonType_DemoApplicants'))) AND ("Extent1"."EntResp+PtrCustodianID"="Extent2"."Pers+id") WHERE ("Extent1"."EntitySubtype"='PersonType_Applicant') OR ("Extent1"."EntitySubtype"='PersonType_DemoApplicants')) AS "Project1" ORDER BY "Project1"."Pers+id" ASC OFFSET 0 LIMIT 15 LEFT OUTER JOIN "public"."Pers++PhotoF" AS "Extent3" ON "Project1"."Pers+id"="Extent3"."Pers+id"


The error is that the second JOIN statement is not integrated correctly in the resulting query, but rather simply appended.

Thanks for your efforts

Followup

Message
Date: 2012-07-02 02:27
Sender: Josh Cooley

I believe I've got a solution and i'm running through tests for the next release.
Date: 2012-06-05 12:36
Sender: Constantinus van der Kruijs

hey, has something been done about this? it's been a while :-)
Date: 2010-12-01 22:06
Sender: Atam Panday

I tried my code:

query.Include("photos").OrderBy(orderBy).Skip(start).Take(length

with devart provider 5.00.58 (29-Nov-2010), and it works.
Date: 2010-12-01 21:40
Sender: Atam Panday

ps. i'm using 2.0.11.0
Date: 2010-12-01 21:38
Sender: Atam Panday

Hi Constantinus ,

i second this. Also, the following is not even working for me, can you check this for me?

query.Include("photos").OrderBy("name").Skip(20).Take(10);

or

query.OrderBy("name").Skip(20).Take(10).Include("photos");

So just 1 include, but together with a orderby. This is usually needed for paging.

Can someone please take a look...

Thanks

Atam

Attached Files:

Changes:

Field Old Value Date By
summaryInvalid Sql Query Generation when Using &quot;Include&quot; (Join) in Entity Framework 42012-06-21 12:27kruijs
summaryInvalid Sql Query Generation when Using "Include" (Join) in Entity Framework 42012-06-05 12:36kruijs
Powered By FusionForge