为什么Entity Framework在SELECT语句中会生成JOIN？

Question

我在一个C#应用程序中使用Entity Framework并采用延迟加载。我们发现一个对CPU影响极大的查询，仅计算一个总和。调试Entity Framework生成的查询时，它创建了一个INNER JOIN (SELECT ...，这不是高效的。当我手动将查询更改为适当的JOIN时，查询时间从1.3秒降至0.03秒。

让我通过一个简化版本的代码来说明它。

public decimal GetPortfolioValue(Guid portfolioId)
{
   var value = DbContext.Portfolios
        .Where( x => x.Id.Equals(portfolioId) )
        .SelectMany( p => p.Items
            .Where( i => i.Status == ItemStatusConstants.Subscribed 
                && _activeStatuses.Contains( i.Category.Status ) )
        )
        .Select( i => i.Amount )
        .DefaultIfEmpty(0)
        .Sum();

   return value;
}

这将生成一个查询，它选择总和，但在两个表联接在一起的SELECT上进行内部连接。我创建了一个pastebin 链接用于生成的查询，以避免污染这个问题，但缩短版如下：

SELECT ...
FROM `portfolios` AS `Extent1`
INNER JOIN (SELECT 
               `Extent2`.*,
               `Extent3`.*
            FROM `items` AS `Extent2`
            INNER JOIN `categories` AS `Extent3` ON `Extent3`.`id` = 
`Extent2`.`category_id`) AS `Join1`
ON `Extent1`.`id` = `Join1`.`portfolio_id`
    AND ((`Join1`.`status` = @gp1)
    AND (`Join1`.`STATUS1` IN (@gp2, @gp3, @gp4, @gp5, @gp6)))
WHERE ...

我期望生成的查询（耗时0.03秒而不是1.3秒）应该是类似以下的内容。

SELECT ...
FROM `portfolios` AS `Extent1`
INNER JOIN `items` AS `Extent2` ON `Extent2`.`portfolio_id` = `Extent1`.`id`
INNER JOIN `categories` AS `Extent3` ON `Extent3`.`id` = `Extent2`.`category_id`
    AND ((`Extent2`.`status` = @gp1)
    AND (`Extent3`.`status` IN (@gp2, @gp3, @gp4, @gp5, @gp6)))
WHERE ...

我怀疑是由于使用了.SelectMany，但我不知道应该如何重写 LINQ 查询以使其更有效率。至于实体框架，则链接属性是虚拟的，并且配置了外键：

public class Portfolio
{
   public Guid Id { get; set; }
   public virtual ICollection<Item> Items { get; set; }
}

public class Item
{
   public Guid Id { get; set; }
   public Guid PortfolioId { get; set; }
   public Guid CategoryId { get; set; }
   public decimal Amount { get; set; }
   public string Status { get; set; }
   public virtual Portfolio Portfolio { get; set; }
   public virtual Category Category { get; set; }
}

public class Category
{
   public Guid Id { get; set; }
   public string Status { get; set; }
   public virtual ICollection<Item> Items { get; set; }
}

任何帮助都将不胜感激！

- Jules

---

这些只是JOINs。服务器应该能够生成一个不加载这些字段的执行计划。这两个查询的执行计划是什么？你使用的是哪个MySQL版本？查询性能由执行计划控制，而执行计划又取决于查询和数据统计信息。另一方面，MySQL 5.7.x版本甚至在次要版本之间也有显着差异。 - Panagiotis Kanavos

SQL查询是由数据库提供程序生成的，而不是EF。您使用了哪个提供程序？Pomelo？还是其他什么？或者是Connector/NET？如果您使用了Connector/NET，是否尝试过使用其他东西，比如Pomelo？ - Panagiotis Kanavos

也许你可以尝试稍微修改一下查询语句。假设portfolioId是唯一的，你可以像这样修改查询语句：var value = DbContext.Portfolios .First(x => x.Id.Equals(portfolioId)).Items... - VDN

它创建了一个不高效的 INNER JOIN (SELECT ...)。为什么？除非查询优化器未能简化查询，否则这等同于将所有表连接在一起。如果服务器在此操作失败，则是服务器错误。查询中真正奇怪的构造是"(SELECT 1 AS 'X') AS 'SingleRowTable1' LEFT OUTER JOIN ...ON 1=1"。这是一些未记录的技巧，在后来的版本中反而适得其反了吗？ - Panagiotis Kanavos

再问一遍，你使用的是哪个提供程序？如果“Item”不是实体，则提供程序本身应该会发出警告。它不应该基于反射发出SQL查询。它太聪明了，自作聪明。 - Panagiotis Kanavos

@PanagiotisKanavos 我确实在使用Connector/NET。MySQLv5.7.22。SELECT 1 AS 'X'并不让我太担心。我认为.DefaultIfEmpty(0)导致了这种情况，并将该操作推送到DB服务器上。虽然在MySQL中有更好的方法来做到这一点，但我可以理解他们必须在查询生成中采取一些捷径。但缺乏适当的JOIN是没有意义的。 - Jules

Answer 1

由于您不需要来自Portfolio的任何内容，只需按PortfolioId进行过滤，因此您可以直接查询PortfolioItems。假设您的DbContext具有包含所有投资组合中所有项目的DbSet，可能如下所示：

var value = DbContext.PortfolioItems
                     .Where(i => i.PortfolioId == portfolioId && i.Status == ItemStatusConstants.Subscribed && _activeStatuses.Contains(i.Category.Status))
                     .Sum(i=>i.Amount);                 

我相信如果您直接使用合适的Queryable.Sum重载，就不需要使用DefaultIfEmpty或select语句。

编辑：尝试了两个不需要暴露DbSet的不同LINQ查询。

第一个查询基本上与您的查询相同：

var value2 = dbContext.Portfolios
    .Where(p => p.Id == portfolioId)
    .SelectMany(p => p.Items)
    .Where(i => i.Status == "A" && _activeStatuses.Contains(i.Category.Status))
    .Select(i=>i.Amount)
    .DefaultIfEmpty()
    .Sum();

分析了 SQL Server 中的查询（手头没有 MySql），并生成了一句丑陋的语句（参数已替换，引号已取消转义以进行测试）：

SELECT [GroupBy1].[a1] AS [C1] 
FROM   (SELECT Sum([Join2].[a1_0]) AS [A1] 
    FROM   (SELECT CASE 
                     WHEN ( [Project1].[c1] IS NULL ) THEN Cast( 
                     0 AS DECIMAL(18)) 
                     ELSE [Project1].[amount] 
                   END AS [A1_0] 
            FROM   (SELECT 1 AS X) AS [SingleRowTable1] 
                   LEFT OUTER JOIN 
                   (SELECT [Extent1].[amount] AS [Amount], 
                           Cast(1 AS TINYINT) AS [C1] 
                    FROM   [dbo].[items] AS [Extent1] 
                           INNER JOIN [dbo].[categories] AS 
                                      [Extent2] 
                                   ON [Extent1].[categoryid] = 
                                      [Extent2].[id] 
                    WHERE  ( N'A' = [Extent1].[status] ) 
                           AND ( [Extent1].[portfolioid] = 
                                 'E2CC0CC2-066F-45C9-9D48-543D92C4C92E' ) 
                           AND ( [Extent2].[status] IN ( N'A', N'B', N'C' ) 
                               ) 
                           AND ( [Extent2].[status] IS NOT NULL )) AS 
                   [Project1] 
                                ON 1 = 1) AS [Join2]) AS [GroupBy1] 

如果我们移除"Select"和"DefaultIfEmpty"方法，并将查询重写如下：

var value = dbContext.Portfolios
    .Where(p => p.Id == portfolioId)
    .SelectMany(p => p.Items)
    .Where(i => i.Status == "A" && _activeStatuses.Contains(i.Category.Status))
    .Sum(i => i.Amount);

生成的句子更清晰：

SELECT [GroupBy1].[a1] AS [C1] 
FROM   (SELECT Sum([Extent1].[amount]) AS [A1] 
    FROM   [dbo].[items] AS [Extent1] 
           INNER JOIN [dbo].[categories] AS [Extent2] 
                   ON [Extent1].[categoryid] = [Extent2].[id] 
    WHERE  ( N'A' = [Extent1].[status] ) 
           AND ( [Extent1].[portfolioid] = 
                 'E2CC0CC2-066F-45C9-9D48-543D92C4C92E' ) 
           AND ( [Extent2].[status] IN ( N'A', N'B', N'C' ) ) 
           AND ( [Extent2].[status] IS NOT NULL )) AS [GroupBy1] 

结论：我们不能依赖LINQ提供程序来创建优化的查询。即使在考虑生成的SQL语句之前，LINQ查询也必须被分析和优化。

- Pepelui360

---

是的，但是这些项在DbSet上没有暴露出来。而且我不想暴露它们，因为它们本身不应该存在。我想强制代码/开发人员通过Portfolio进行访问。 - Jules

@Jules 然后使用以下代码：var value = DbContext.Portfolio.First(p => p.Id == portfolioId).Items.Where(i => i.Status == ItemStatusConstants.Subscribed && _activeStatuses.Contains(i.Category.Status)) .Sum(i=>i.Amount); - VDN

但是Items没有在DbSet上公开，这是什么意思？EF（或提供程序）无法为非实体生成查询。您使用的是哪个提供程序？无论如何，如果您只想查询这些项，请将它们添加为DbSet。 - Panagiotis Kanavos

我认为@PanagiotisKanavos是正确的，应该添加DbSet。此外，如果您的DbContext中只有顶级实体的DbSet，则LINQ查询可能会变得不必要地复杂。要查询顶级实体以下五个级别的集合，您将不得不遍历前面的四个级别。这乘以相当数量的查询可能会使事情变得混乱。 - Pepelui360

@Pepelui360 我最终以不同的方式重写了它，但成功生成了一个干净的查询，从1.3秒降至几毫秒。基本上我是在 .SelectMany 响应上执行 .Where，而不是在 .SelectMany 方法内部执行。这样一个小改变竟然会带来如此大的差异，真是令人惊讶。这让我想知道还有多少其他的 LINQ 查询可以进行优化，因为只有当事情真的失控时才会注意到它们... - Jules