We are in the process of comparing pros and cons of ODP.NET and Devart's docConnect for Oracle. We are trying to figure out whether or not we will be able to run our EF queries. We have a type of query that fails with Devart's data provider and they claim it is an Oracle limitation.
Is there a limitation of Oracle that causes the following query to throw an error? If so, how would one go about restructuring the query to get the same result from Oracle?
var entityFields = _Context.Fields
.Where(f => f.ObjectFields
.Any(otf => otf.Object.Things
.Any(p => p.ThingID == thingID))
CODE: SELECT ALL
SELECT "Extent1"."FieldID" FROM "Field" "Extent1"
SELECT 1 AS C1 FROM (
FROM "ObjectField" "Extent2"
WHERE "Extent1"."FieldID" = "Extent2"."FieldID" )
WHERE EXISTS (
SELECT 1 AS C1 FROM "Thing" "Extent3"
WHERE ("Project1"."ObjectID" = "Extent3"."ObjectID")
AND ("Extent3"."ThingID" = :p__linq__0) ) ))
SELECT 1 AS C1 FROM "Definition" "Extent4"
WHERE "Extent1"."FieldID" = "Extent4"."FieldID" ))
Out of curiousity, have you tried tossing the output SQL into SQL Developer and running it direcctly against the database? What errors is that throwing up?
There are some types of queries that Oracle can do that can't really be done in EF currently (Spatial stuff and CONNECT BY PRIOR are typical examples). In those cases I either run the SQL directly or use a stored procedure and call that, depending on the situation. This query may be one of those. In this case though you might be able to rewrite the EF query a different way, but without the schema I'm really not sure what that query is trying to do so don't have much advice there.
ORA-00904: "Extent1"."FieldID": invalid identifier
Extent1, Extent2, Extent3, Extent4 and Project1 are all alias' created by EF. None of them are actually tables in the scheme. They are each defined in the query.
Regarding this error Devart says
Please refer to http://www.devart.com/dotconnect/oracle/faq.html#q56. We recommend you to reconstruct your EF query to avoid the situation when a column from the outer query is used in the subquery of level 2 and deeper. EF query should be customised/modified for every particular case when someone encounters this limitation of Oracle server: http://forums.devart.com/viewtopic.php?t=22349.