Limitation of query capability or necessary config adjustment???

[cResults]

This entity framework query :

Code: Select all

var entityCatalogFields = _Context.Fields
           .Where(f => f.ObjectFields
                           .Any(otf => otf.Object.Things
                                            .Any(p => p.ThingID == partyID))
                    && f.Definitions.Any())
           .Select(f=>f.FieldID)
           .ToList();

Produces this PL/SQL:

Code: Select all

SELECT "Extent1"."FieldID" FROM "Field" "Extent1" 
WHERE ( 
	EXISTS (
		SELECT 1 AS C1 FROM ( 
		SELECT "Extent2"."ObjectID" 
		FROM "ObjectField" "Extent2" 
		WHERE "Extent1"."FieldID" = "Extent2"."FieldID" )  
	"Project1" 
WHERE  EXISTS (
		SELECT 1 AS C1 FROM "Thing" "Extent3" 
		WHERE ("Project1"."ObjectID" = "Extent3"."ObjectID") 
			AND ("Extent3"."ThingID" = :p__linq__0) ) )) 
			AND ( 
EXISTS (
		SELECT 1 AS C1 FROM "Definition" "Extent4" 
		WHERE "Extent1"."FieldID" = "Extent4"."FieldID" ))

Which throws:

Code: Select all

ErrorCode -2147467259

ORA-00904: "Extent1"."FieldID": invalid identifier

   at Devart.Data.Oracle.t.d()
   at Devart.Data.Oracle.ar.h()
   at Devart.Data.Oracle.ar.c()
   at Devart.Data.Oracle.aa.a(Int32 A_0, by A_1)
   at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Oracle.Entity.w.a(CommandBehavior A_0)
   at Devart.Common.Entity.o.b(CommandBehavior A_0)
   at Devart.Data.Oracle.Entity.w.b(CommandBehavior A_0)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

We are using EF 5.0 & dotConnect 7.2.114.

I know from reviewing many queries produced by Linq to Entity Framework that this is a common syntax. Is there a configuration setting that I might have missed that when properly set will make this query work?

[Shalex]

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.