EF Core中GroupBy-Sum LINQ生成意外SQL，如何避免子查询？

我编写了如下LINQ查询：

var data = await _context.Answers
    .Where(q => q.Question != null)
    .GroupBy(q => q.Choice)
    .Select(g => new
    {
        g.Key,
        Data = g.Sum(q => q.Question.Weight)
    })
    .ToListAsync();

我的实体类定义如下：

public class Answer
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public Choice? Choice { get; set; } // 数据库中值为0、1、2

    #region 关联关系

    public Guid? QuestionId { get; set; }
    public virtual Question? Question { get; set; }

    #endregion
}

public class Question
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public ushort Weight { get; set; }
}

上述LINQ查询生成了如下SQL：

SELECT [a].[Choice] AS [Key],
       (
           SELECT COALESCE(SUM(CAST([t1].[Weight] AS int)), 0)
           FROM [Answers] AS [a0]
               LEFT JOIN [Questions] AS [t0]
                   ON [a0].[QuestionId] = [t0].[Id]
               LEFT JOIN [Questions] AS [t1]
                   ON [a0].[QuestionId] = [t1].[Id]
           WHERE ([t0].[Id] IS NOT NULL)
                 AND (
                         [a].[Choice] = [a0].[Choice]
                         OR (
                                ([a].[Choice] IS NULL)
                                AND ([a0].[Choice] IS NULL)
                            )
                     )
       ) AS [Data]
FROM [Answers] AS [a]
    LEFT JOIN [Questions] AS [t]
        ON [a].[QuestionId] = [t].[Id]
WHERE [t].[Id] IS NOT NULL
GROUP BY [a].[Choice]

注意事项：

• EF Core版本：Microsoft.EntityFrameworkCore.SqlServer 7.0.2
• 数据库：SQL Server 2019

我无法理解为什么SUM函数会生成子查询，我期望生成的SQL如下：

SELECT [a].[Choice] AS [Key],
       COALESCE(SUM(CAST([t].[Weight] AS int)), 0) AS [Data]
FROM [Answers] AS [a]
    LEFT JOIN [Questions] AS [t]
        ON [a].[QuestionId] = [t].[Id]
WHERE [t].[Id] IS NOT NULL
GROUP BY [a].[Choice]

是否可以避免生成SQL中的子查询？

解决方案 #

可以通过调整LINQ查询的写法，让EF Core生成更简洁的SQL，避免子查询。核心是提前明确聚合所需的字段，避免分组后再访问导航属性导致的嵌套查询。

写法1：使用Join提前关联实体

var data = await _context.Answers
    .Join(_context.Questions,
          answer => answer.QuestionId,
          question => question.Id,
          (answer, question) => new { answer.Choice, question.Weight })
    .GroupBy(x => x.Choice)
    .Select(g => new
    {
        g.Key,
        Data = g.Sum(x => x.Weight)
    })
    .ToListAsync();

写法2：先投影字段再分组

var data = await _context.Answers
    .Where(q => q.Question != null)
    .Select(q => new { q.Choice, q.Question.Weight })
    .GroupBy(x => x.Choice)
    .Select(g => new
    {
        g.Key,
        Data = g.Sum(x => x.Weight)
    })
    .ToListAsync();

这两种写法都会生成你期望的简洁SQL。原因是EF Core在分组后访问导航属性时，可能因无法确定关联唯一性而生成子查询；而提前投影或Join明确了聚合字段后，EF Core就能直接生成GROUP BY后的SUM聚合逻辑。

内容的提问来源于stack exchange，提问作者Prathamesh Katkar