The query that EF generates from the linq query below works against Sql Server, but not against Oracle.
Code: Select all
var query = ctx.Contacts.Where (c => c.ContactType.FormTypes.Select (ft => ft.FormTypeID).Except(c => c.Forms.Select(f => f.FormTypeID)).Any());
The error that Oracle is throwing is ORA-00904: "Extent1"."ContactID": invalid identifier.
The problem is that Oracle apparently doesn't support referencing a table column from a query in a nested subquery of level 2 and deeper. The line that Oracle throws on is in the Except (or minus) sub query that is referencing "Extent1"."ContactID". "Extent1" is the alias for Contact that is defined at the top level of the query. Here is Devart's explanation of the Oracle limitationhttp://www.devart.com/dotconnect/oracle/faq.html#q56.
The way that I've resolved this issue for many queries is by re-writing them to move relationships between tables out of the Where() predicate into the main body of the query using SelectMany() and in some cases Join(). This tends to flatten the query being sent to the database server and minimizes or eliminates the sub queries produced by EF. Here is a similar issue solved using a left outer joinhttp://stackoverflow.com/questions/1404 ... -using-ef5.
Any ideas on how to re-write this query will be much appreciated. The objective is a query that returns Contacts missing Forms of a FormType required by the Contact's ContactType that works against Oracle and Sql Server.
Schema image referred to above can be see here http://stackoverflow.com/questions/1412 ... ing-ef-5-0