CODEWITHSUNDEEP
performanceentity-frameworklinq
James
James

Reputation: 349

SQL generated by Entity Framework is missing a join

We noticed this query has a high amount of reads:

var var1 = "535d1a11-1c2b-467a-3333-222aaa9b1fd4";
var var2 = 117;
var test = (from t1 in contextObj.Table1
    join t2 in contextObj.Table2
        on t1.Column2 equals t2.Column1
    join t3 in contextObj.Table3 on t2.Column2 equals t3.Column1
    where t3.Column1 == var1 && t2.Column3 == var2
                             && t2.Column2 == var1
    select t1).ToList();

This is because the SQL generated by Entity Framework is missing a join: 

exec sp_executesql N'SELECT 
    [Extent1].[Column1] AS [Column1], 
    [Extent1].[Column2] AS [Column2], 
    [Extent1].[Column3] AS [Column3], 
    [Extent1].[Column4] AS [Column4], 
    [Extent1].[Column5] AS [Column5], 
    [Extent1].[Column6] AS [Column6]
    FROM  [dbo].[Table1] AS [Extent1]
    INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Column2] = [Extent2].[Column1]
    WHERE ([Extent2].[Column2] = @p__linq__0) AND ([Extent2].[Column3] = @p__linq__1) AND ([Extent2].[Column2] = @p__linq__2)',N'@p__linq__0 nvarchar(4000),@p__linq__1 int,@p__linq__2 nvarchar(4000)',@p__linq__0=N'535d5b16-1c2b-467a-9022-933ebf9b1fd4',@p__linq__1=117,@p__linq__2=N'535d5b16-1c2b-467a-9022-933ebf9b1fd4'

Database create script: https://gist.github.com/jbouwens/85e8840d799b8178ee30feb389fbc4ac

Why did EF not include this join/what can I do to prevent this in the future? Thanks!

Upvotes: 2

Views: 229

Answers (2)

user1011627
user1011627

Reputation: 1811

In your query, the where clause is comparing t3.Column1 == var1 and t2.Column2 == var1, but since your join already takes into account that t3.Column1 == t2.Column2, EF is automagically removing what it assumes is non-needed join. Since the table structure is not ideal, the solution has been determined to return a column from table3 to force EF into joining to the table. 

var test = (from t1 in contextObj.Table1new 
    join t2 in contextObj.Table2
        on t1.Column2 equals t2.Column1
    join t3 in contextObj.Table3 on t2.Column2 equals t3.Column1
    where t3.Column1 == var1 && t2.Column3 == var2
    select new { t1, t3.Column1 }).ToList()

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

I repro'd this and EF is "trusting" your declared foreign keys in its query generation, which SQL Server will do too (if you have them enforced and checked).

EF is transforming the predicate t3.Column1 == var1 to [Extent2].[Column3] = @p__linq__1 since you joined those columns in the query, and the Foreign Key guarantees that the join will change the rowcount.

Upvotes: 1

Related Questions