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
Master.Childs
Child.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:
...
FROM MASTER "Extent1"
INNER JOIN CHILD "Extent2" ON
EXISTS (SELECT 1 AS C1 FROM
( SELECT 1 FROM DUAL) "SingleRowTable1"
INNER JOIN MASTER"Extent3"
ON 1 = 1
WHERE
("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.......
Any help or advice to avoid such trouble
Tnx
Roman