c# - 为什么 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 here对于生成的查询不污染这个问题，但一个缩短的版本将是:

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; }
}

如有任何帮助，我们将不胜感激!

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 查询进行分析和优化。