实体框架LINQ to Entities加入查询超时 [英] Entity Framework LINQ to Entities Join Query Timeout

问题描述

我正在执行以下LINQ to Entities查询，但是它被卡住，直到超时才返回响应。我在SQL Server上执行相同的查询，并在3秒内返回92000。

I am executing the following LINQ to Entities query but it is stuck and does not return response until timeout. I executed the same query on SQL Server and it return 92000 in 3 sec.

            var query = (from r in WinCtx.PartsRoutings
                     join s in WinCtx.Tab_Processes on r.ProcessName equals s.ProcessName
                     join p in WinCtx.Tab_Parts on r.CustPartNum equals p.CustPartNum
                     select new { r}).ToList();

SQL生成：

SELECT [ I omitted columns]
    FROM   [dbo].[PartsRouting] AS [Extent1]
INNER JOIN [dbo].[Tab_Processes] AS [Extent2] ON ([Extent1].[ProcessName] = [Extent2].[ProcessName]) OR (([Extent1].[ProcessName] IS NULL) AND ([Extent2].[ProcessName] IS NULL))
INNER JOIN [dbo].[Tab_Parts] AS [Extent3] ON ([Extent1].[CustPartNum] = [Extent3].[CustPartNum]) OR (([Extent1].[CustPartNum] IS NULL) AND ([Extent3].[CustPartNum] IS NULL))

PartsRou​​ting表有100,000个以上的记录，零件= 15000+，进程= 200。

PartsRouting Table has 100,000+ records, Parts = 15000+, Processes = 200.

我尝试过太多在网上找到的东西，但是对于我来说，没有什么可以帮助我实现与SQL相同性能的结果。

I tried too many things found online but nothing worked for me as to how I can achieve the result with same performance of SQL.

推荐答案

根据评论，看起来像是由 OR 与 IS NULL EF SQL转换器生成的连接条件。它们被添加到EF中，以便模拟不同于 = 的运算符语义c $ c> NULL 值。

Based on the comments, looks like the issue is caused by the additional OR with IS NULL conditions in joins generated by the EF SQL translator. They were added in EF in order to emulate the C# == operator semantics which are different from SQL = for NULL values.

您可以通过 UseDatabaseNullSemantics 属性（它 false ）

WinCtx.Configuration.UseDatabaseNullSemantics = true;

不幸的是，这还不够，因为它修复了正常的比较运算符，但是他们根本忘了做同样的事情用于连接条件。

Unfortunately that's not enough, because it fixes the normal comparison operators, but they simply forgot to do the same for join conditions.

如果您正在使用连接过滤（似乎），您可以用LINQ 替换任何条件转换为SQL EXISTS 和现在的数据库查询优化器正在对待它，就像它是一个内部连接一样：

In case you are using joins just for filtering (as it seems), you can replace them with LINQ Any conditions which translates to SQL EXISTS and nowadays database query optimizers are treating it the same way as if it was an inner join:

var query = (from r in WinCtx.PartsRoutings
             where WinCtx.Tab_Processes.Any(s => r.ProcessName == s.ProcessName)
             where WinCtx.Tab_Parts.Any(p => r.CustPartNum == p.CustPartNum)
             select new { r }).ToList();

您也可以考虑使用 select r 因为创建具有单个属性的匿名类型只是引入额外的内存开销，没有任何优势。

You might also consider using just select r since creating anonymous type with single property just introdeces additional memory overhead with no advantages.

更新：看最新的评论，你 do 需要连接表中的字段（这就是为什么不要忽略有问题的查询的相关部分的原因）。在这种情况下，您可以尝试使用替代连接语法，其中子句：

Update: Looking at the latest comment, you do need fields from joined tables (that's why it's important to not omit relevant parts of the query in question). In such case, you could try the alternative join syntax with where clauses:

WinCtx.Configuration.UseDatabaseNullSemantics = true;
var query = (from r in WinCtx.PartsRoutings
             from s in WinCtx.Tab_Processes where r.ProcessName == s.ProcessName
             from p in WinCtx.Tab_Parts where r.CustPartNum == p.CustPartNum
             select new { r, s.Foo, p.Bar }).ToList();

这篇关于实体框架LINQ to Entities加入查询超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！