Problem with JOIN in Entity SQL

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Posts: 55
Joined: Wed 12 Aug 2009 05:47

Problem with JOIN in Entity SQL

Post by HCRoman » Thu 01 Apr 2010 12:46

Hi devart team,

I am not sure, if this is a devart problem or an microsoft problem...

(and it is not a first april joke!)

If I use join with entity sql, the generated SQL is verry poor generated, with LINQ to entity, the join is generated as expected.

Try this:

You have two tables in an ORACLE DB:

MASTER withprimary key ID
CHILD with primary key ID and MasterId as reference

In the resulting entityModel has two entities

Master with Association to his childs.
Child with Reference to his Master


if you try an ObjectQuery and you try to join Master and Child:

I will not have projection query in this situation.
I am usind Entity SQL, couse the WHERE clouse is build on runtime and depends on user input, so I am building with StringBuilder my verry dynamic WHERE....

By the way, why does the ObjectQuery.Extension

.Where(string where, ObjectParameter[] parameters)
not work with devart????
(Exception NotSupported...)

first join Problem:

you can not write:

from m in context.Master join c in context.Child on m.ID = c.MasterID

(Error, MasterID is not a property of Child)

:?: Question: How can I create the property and the Association in the entity model???

instead you have to write

from m in context.Master join c in context.Child on m.ID = c.Master.ID

the resulting JOIN SQL is as expected:


FROM master "Extent1" INNER JOIN child "Extent2" ON ("Extent1".ID = "Extent2".ID)


But if you write the same query in Entity SQL:

(same problem as above, you can not use on m.ID = c.masterID)

you have to write:

SELECT VALUE m FROM Master as m JOIN Child as c on
m.ID = c.Master.ID

Now the resulting SQL looks like:

:roll: :roll: :roll: :roll:
( SELECT 1 FROM DUAL) "SingleRowTable1"
ON 1 = 1
("Extent2".ID= "Extent3".ID) AND
("Extent1".ID = "Extent3".ID)

And Oracle waste your time......
Exists and Oracle are soooo slow....
Instead DISTINCT and JOIN are quick

:?: :?: :?:
Any solution for this two problems?

I have found a workaround for me.... but it hurts

I created an Query in the EntityModel as CHILDVIEW with Select * from Child with no associations but same columns as CHILD
A EntityClass ChildView for this Query and now
MasterID is generated as an Property and now I use this entity as my join collection

SELECT VALUE m FROM Master as m JOIN ChildView as c on
m.ID = c.MasterID

and it works....... :idea: :idea: :wink:

Any help or advice to avoid such trouble :?: :?: :?:



Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 01 Apr 2010 14:25

Could you please send me (support * devart * com, subject "EF: Where extension") a small test project reproducing the error with the Where method?
Foreign Key columns are excluded from the model in EF v1. You can upgrade to EF v4 if this feature is critical for you, Microsoft added support for Foreign Keys.
As for the generated SQL, we are unable to optimize the ExpressionTree which is built from the query before our SQL translation is performed.

Post Reply