时间戳

首页 > 解决方案 > 实体框架生成的 SQL 缺少连接

performance - 实体框架生成的 SQL 缺少连接

问题描述

我们注意到这个查询有大量的读取：

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();

这是因为 Entity Framework 生成的 SQL 缺少连接：

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'

数据库创建脚本：https ://gist.github.com/jbouwens/85e8840d799b8178ee30feb389fbc4ac

为什么 EF 不包含此连接/我可以做些什么来防止这种情况发生？谢谢！

标签： performanceentity-frameworklinq

解决方案

在您的查询中，where 子句比较 t3.Column1 == var1 和 t2.Column2 == var1，但由于您的连接已经考虑到 t3.Column1 == t2.Column2，EF 会自动删除它假定的非- 需要加入。由于表结构不理想，所以确定了解决方案，从table3返回一列，强制EF加入表。

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

推荐阅读