EF 7.0.2 LINQ查询中如何移除SUM外层的COALESCE函数

原始SQL中，Weight字段遇到空批次或权重为NULL的lager时会返回NULL，便于识别问题批次。但使用EF Core 7.0.2编写LINQ查询时，EF会自动在SUM函数外层添加COALESCE，导致问题批次的Weight被转为容器重量。调整JOIN方式和空值检查后，生成的SQL仍带有COALESCE，求彻底移除的方法。

原始SQL #

DECLARE  @documentId INT = 8
SELECT 
    i.[status]      AS [status],
    i.[barcode]     AS [barcode],
    i.[containerId] AS [containerTypeId],
    (
        SUM(ISNULL(lp.[Weight], 0) * il.[factCount]) + 
        ISNULL(lpt.[Weight], 0)
    )               AS [weight],
    i.[comment]     AS [comment]
FROM [Items]                   AS i    WITH(NOLOCK)
LEFT JOIN [dbo].[ItemsLines]   AS il   WITH(NOLOCK) ON i.[id]          = il.[itemsId] 
LEFT JOIN [dbo].[LagerOptions] AS lp   WITH(NOLOCK) ON lp.[lagerId]    = il.[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS lpt  WITH(NOLOCK) ON lpt.[lagerId]   = i.[containerId]
WHERE @documentId = i.[documentId] 
GROUP BY i.[status], i.[barcode], i.[containerId], i.[comment], lpt.[Weight]

最初的LINQ查询 #

var lots = await (

    from i in _dbContext.Items
    join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptTable 
    from lpt in lptTable.DefaultIfEmpty()

    // The sum of all lagers for current lot
    let lotWeight = (
        from il in _dbContext.ItemsLines
        join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpTable 
        from lp in lpTable.DefaultIfEmpty()
        where il.ItemsId == i.Id
        select (il.FactCount * lp.Weight.GetValueOrDefault(0))
    ).Sum()

    where i.DocumentsId == documentId
    select new
    {
        Status = i.Status,
        Barcode = i.Barcode,
        ContainerTypeId = i.ContainerId,
        // total weight with container Weight
        Weight = lotWeight + lpt.Weight.GetValueOrDefault(0),
        //
        Comment = i.Comment
    }
).ToListAsync();

修改后的LINQ查询 #

var query = 
    from i in _dbContext.Items
    join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
    from il in ilj.DefaultIfEmpty()
    join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
    from lp in lpj.DefaultIfEmpty()
    join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
    from lpt in lptj.DefaultIfEmpty()
    where i.DocumentsId == documentId
    group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
    select new 
    {
        g.Key.Status,
        g.Key.Barcode,
        ContainerTypeId = g.Key.ContainerId,
        Weight = g.Sum(x => ((decimal?)x.lp.Weight) ?? 0 * x.il.FactCount) + g.Key.Weight ?? 0,
        g.Key.Comment
    };

生成的带COALESCE的SQL #

SELECT 
    [t].[status] AS [Status], 
    [t].[barcode] AS [Barcode], 
    [t].[containerId] AS [ContainerTypeId], 
    COALESCE(SUM(COALESCE([l].[weight], 0.0) * [t0].[factCount]), 0.0) + 
    COALESCE([l0].[weight], 0.0) AS [Weight], 
    [t].[comment] AS [Comment]
FROM [dbo].[Items] AS [t]
LEFT JOIN [dbo].[ItemsLines] AS [t0] ON [t].[id] = [t0].[itemsId]
LEFT JOIN [dbo].[LagerOptions] AS [l] ON [t0].[lagerId] = [l].[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS [l0] ON [t].[containerId] = [l0].[lagerId]
WHERE [t].[documentsId] = @__documentId_0
GROUP BY [t].[status], [t].[barcode], [t].[containerId], [t].[comment], [l0].[weight]

解决方案 #

EF自动添加COALESCE的核心原因：默认的Sum()方法返回非可空数值类型（如decimal），当分组后无匹配行时，SQL原生SUM会返回NULL，但EF需要将NULL转为对应数值类型的默认值（0），因此自动包裹COALESCE。

要实现和原始SQL一致的行为，需让SUM结果保持可空类型：

1. 在Sum的lambda表达式中，将计算结果显式转为可空decimal（decimal?）
2. 保留空值可能性，避免使用GetValueOrDefault()或?? 0这类强制转非空的操作

修改后的最终LINQ查询：

var query = 
    from i in _dbContext.Items
    join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
    from il in ilj.DefaultIfEmpty()
    join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
    from lp in lpj.DefaultIfEmpty()
    join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
    from lpt in lptj.DefaultIfEmpty()
    where i.DocumentsId == documentId
    group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
    select new 
    {
        g.Key.Status,
        g.Key.Barcode,
        ContainerTypeId = g.Key.ContainerId,
        // 显式将Sum结果转为可空decimal，阻止EF添加外层COALESCE
        Weight = g.Sum(x => (decimal?)(x.lp.Weight ?? 0 * x.il.FactCount)) + (g.Key.Weight ?? 0),
        g.Key.Comment
    };

调整后生成的SQL会移除SUM外层的COALESCE，当无匹配的ItemsLines或LagerOptions时，SUM返回NULL，最终Weight字段也会是NULL，与原始SQL逻辑完全一致，便于识别问题批次。

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