invalid SQL generated with Contains & references

[efcoyote]

You have the following tables:

Project:
ID | Leader
1 | 2

Leader:
ID | Location
2 | 5

Location:
ID | Name
5 | foobar

They referenced by foreign keys in the following way:
Project > Leader > Location

Code: Select all

IEnumerable<int> locationIds = new List<int> { 5 };

var filteredLocations = from l in context.Location
                        where locationIds.Contains(l.Id)
                        select l.Id;

var projects = from p in context.Project
                where filteredLocations.Contains(p.Leader.Location.Id)
                select p.Id;

var result = projects.ToList();

This results in a query like:

Code: Select all

SELECT
"Extent1".ID
FROM PROJECT "Extent1"
WHERE  EXISTS (SELECT
        1 AS C1
        FROM ( SELECT
                "Extent2".ID AS ID1,
                FROM  LOCATION "Extent2"
                INNER JOIN LEADER "Extent3" ON "Extent2".ID = "Extent3".LOCATION
                WHERE "Extent1".LEADER = "Extent3".ID
        )  "Filter1"
        WHERE "Filter1".ID1 = 1
)

Error:
Devart.Data.Oracle.OracleException: ORA-00904: "Extent1"."LEADER": invalid identifier

Notes:
With MSSQL-Express the query looks equal but the database is able to resolve "Extend1" in the second subquery. A possible fix is to call ToList after the first query, which prevent generating a second subquery. I know that you can rewrite the query in multiple ways to get it working - it's just to demostrate the invalid mapping from linq to sql.

Versions:
EntityFramework 6.1.1
Devart Oracle 8.4.215.0
Oracle Server 11g (11.2.0.3.0)

[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.