Limitation of query capability or necessary config adjustment???

Limitation of query capability or necessary config adjustment???

Postby cResults » Thu 29 Nov 2012 01:50

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?
cResults
 
Posts: 17
Joined: Fri 09 Nov 2012 17:52

Re: Limitation of query capability or necessary config adjustment???

Postby Shalex » Thu 29 Nov 2012 15:12

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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle