invalid SQL generated with Contains & references

invalid SQL generated with Contains & references

Postby efcoyote » Tue 19 Aug 2014 12:40

You have the following tables:

ID | Leader
1 | 2

ID | Location
2 | 5

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

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

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.

EntityFramework 6.1.1
Devart Oracle
Oracle Server 11g (
Posts: 5
Joined: Tue 19 Aug 2014 12:22

Re: invalid SQL generated with Contains & references

Postby Shalex » Wed 20 Aug 2014 11:18

Please refer to 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:
Devart Team
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Return to dotConnect for Oracle