实体框架在使用ANY时生成了非常糟糕的SQL [英] Entity Framework generated really bad SQL when using ANY

问题描述

我正在分析在DB（Azure Sql Server V12）中运行的查询，并发现由Entity Framework（EF 6.0）生成的一些查询对我来说没有意义。他们对于表演真的很不好，我找不到他们在哪里生成。

 （@ p__linq__0 nvarchar（4000））SELECT 
 CASE WHEN（EXISTS（SELECT 
 1 AS [ C1] 
 FROM [dbo]。[SellerPhone] AS [Extent1] 
 WHERE [Extent1]。[Phone] = @ p__linq__0 
））THEN cast（1 as bit）WHEN（NOT EXISTS （SELECT 
 1 AS [C1] 
 FROM [dbo]。[SellerPhone] AS [Extent2] 
 WHERE [Extent2]。[Phone] = @ p__linq__0 
）） （0作为位）END AS [C1] 
 FROM（SELECT 1 AS X）AS [SingleRowTable1] 
  

解决方案：如果您有这样的查询，这意味着您有EF 6.0或更早版本，您正在做一个简单的dbContext.SellerPhones.Any（p => xxx）。只需升级到6.1，生成的查询将会更好。

解决方案

我做了什么Tipx描述，但我也添加了堆栈跟踪以便能够知道生成每个查询的方法：

  Database.Log =（sql => 
 { 
 System.Diagnostics.Debug.WriteLine（System.Reflection.MethodBase.GetCurrentMethod（）。Name）; 
 System.Diagnostics.Debug.WriteLine（sql）; 
}）; 
  

在EF 6.0中，像dbContext.SellerPhones.Any（p => condition）这样的简单查询将生成一个丑陋的查询，具有一个CASE并调用EXISTS两次。

升级到EF 6.1后，查询只有一个EXISTS，执行得更好（我突出显示在生成的查询被更新）。

SELECT
CASE WHEN（EXISTS（SELECT
1 AS [C1]
FROM [dbo ] [SellerPhone] AS [Extent1]
WHERE [Extent1]。[Phone] = @ p__linq__0
））THEN cast（1 as bit）ELSE cast（0 as bit） END AS [C1]
FROM（SELECT 1 AS X）AS [SingleRowTable1]

I'm analyzing the queries that are running in a DB (Azure Sql Server V12) and I found a few queries generated by Entity Framework (EF 6.0) that make no sense to me. They are really bad for performance and I cannot find where they are generated.

(@p__linq__0 nvarchar(4000))SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[SellerPhone] AS [Extent1]
        WHERE [Extent1].[Phone] = @p__linq__0
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[SellerPhone] AS [Extent2]
        WHERE [Extent2].[Phone] = @p__linq__0
    )) THEN cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Solution: if you have a query like this it means you have EF 6.0 or older and you are doing a simple dbContext.SellerPhones.Any(p => xxx). Just upgrade to 6.1 and the generated query will be much better.

解决方案

I did what Tipx described but I also added the stack trace to be able to know which method was generating each query:

Database.Log = (sql => 
            {
                System.Diagnostics.Debug.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
                System.Diagnostics.Debug.WriteLine(sql);
            });

In EF 6.0, a simple query like dbContext.SellerPhones.Any(p => condition) will generate an ugly query that has a CASE and calls the EXISTS twice.

After upgrading to EF 6.1 the query has only ONE EXISTS and performs much better (I highlighted below that part of the generated query that was updated).

SELECT CASE WHEN ( EXISTS (SELECT 1 AS [C1] FROM [dbo].[SellerPhone] AS [Extent1] WHERE [Extent1].[Phone] = @p__linq__0 )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]

这篇关于实体框架在使用ANY时生成了非常糟糕的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！