EF Core 9中Group By与SUM生成异常SQL的优化方案咨询

问题描述 #

我正在学习EF Core和C#，用Lambda编写聚合查询时遇到问题。现有OrderDetail和Item两个实体，当前LINQ代码生成了嵌套子查询的SQL，希望让OrderDetails直接关联Item并在主SELECT中完成SUM聚合，不想手动编写Join，求更优实现方式。

实体定义 #

public class OrderDetail
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public int Qty { get; set; }
    public int ItemId { get; set; }
    public Item? Item { get; set; } // 注：原代码中类名为Item，此处修正导航属性名称匹配类名
}

public class Item
{
    public int Id { get; set; }
    public decimal Weight { get; set; }
    public decimal Volume { get; set; }
}

当前LINQ代码 #

await context.OrderDetail
    .GroupBy(i => i.Date)
    .Select(s => new TestDto()
    {
        Date = s.Key,
        Volume = s.Sum(x => x.Item.Weight),
        Weight = s.Sum(x => x.Item.Volume),
    })
    .AsNoTracking()
    .ToListAsync();

生成的嵌套子查询SQL #

SELECT [d].[Date], 
(
    SELECT COALESCE(SUM([i].[Weight]), 0.0)
    FROM [OrderDetails] AS [d0]
    INNER JOIN [Items] AS [i] ON [d0].[ItemId] = [i].[Id]
    WHERE [d].[Date] = [d0].[Date]) AS [Volume], 
(
    SELECT COALESCE(SUM([i0].[Volume]), 0.0)
    FROM [OrderDetails] AS [d1]
    INNER JOIN [Items] AS [i0] ON [d1].[ItemId] = [i0].[Id]
    WHERE [d].[Date] = [d1].[Date]
)
FROM [OrderDetails] AS [d]
GROUP BY [d].[Date]

优化方案 #

方法1：提前投影关联字段再分组 #

先通过Select将OrderDetail和关联的Item字段一起投影出来，EF Core会自动将导航属性解析为数据库Join，之后再分组聚合，就能生成主查询内的聚合SQL：

await context.OrderDetail
    .Select(od => new 
    {
        od.Date,
        ItemWeight = od.Item.Weight,
        ItemVolume = od.Item.Volume
    })
    .GroupBy(x => x.Date)
    .Select(g => new TestDto()
    {
        Date = g.Key,
        Volume = g.Sum(x => x.ItemWeight),
        Weight = g.Sum(x => x.ItemVolume)
    })
    .AsNoTracking()
    .ToListAsync();

方法2：分组聚合时显式处理导航属性 #

如果不想提前投影，也可以在Sum中显式处理导航属性的null情况（若外键允许为空），EF Core会识别为Join而非子查询：

await context.OrderDetail
    .GroupBy(od => od.Date)
    .Select(g => new TestDto()
    {
        Date = g.Key,
        Volume = g.Sum(od => od.Item != null ? od.Item.Weight : 0m),
        Weight = g.Sum(od => od.Item != null ? od.Item.Volume : 0m)
    })
    .AsNoTracking()
    .ToListAsync();

优化后生成的SQL #

两种方法最终都会生成类似下面的SQL，在主查询中完成Join和聚合，避免嵌套子查询：

SELECT [od].[Date], 
       SUM([i].[Weight]) AS [Volume], 
       SUM([i].[Volume]) AS [Weight]
FROM [OrderDetails] AS [od]
INNER JOIN [Items] AS [i] ON [od].[ItemId] = [i].[Id]
GROUP BY [od].[Date]

关键说明 #

原代码生成嵌套子查询的原因是：分组后在聚合函数中直接访问导航属性，EF Core会将其解析为针对每个分组的独立子查询。而提前投影或显式处理导航属性，会引导EF Core优先执行Join操作，再进行分组聚合。

另外需确保OrderDetail与Item的关联关系在EF Core模型中已正确配置（比如通过Fluent API的HasOne/HasMany），否则导航属性无法被正确解析为数据库Join。

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