如何在LINQ中实现SQL的先子分组再最终分组汇总逻辑？

以下是完全匹配你原SQL逻辑的LINQ实现，分阶段对应SQL的子查询和外层汇总：

步骤1：过滤原始数据并按Discipline分组统计 #

先筛选NA为0或null的记录，按Discipline分组，统计每个分组的TOTAL（非空TagNo数量）、COMP（Completed=1的记录数）、REM（Completed≠1或为null的记录数）：

var disciplineStats = dbContext.WBS
    .Where(w => w.NA == 0 || w.NA == null)
    .GroupBy(w => w.Discipline)
    .Select(g => new 
    {
        Discipline = g.Key,
        TOTAL = g.Count(w => w.TagNo != null), // 对应SQL的Count(TagNo)
        COMP = g.Count(w => w.Completed == 1), // 对应SQL中Count(case when Completed=1 then 1 else null end)
        REM = g.Count(w => w.Completed != 1 || w.Completed == null) // 对应SQL的REM统计逻辑
    });

步骤2：映射Discipline到目标类别并二次汇总 #

将原始Discipline映射为指定的DISCIP类别，再按DISCIP分组汇总所有数值：

var finalResult = disciplineStats
    .Select(x => new 
    {
        DISCIP = x.Discipline switch
        {
            var d when new[] { "ELECTRICAL", "F&G", "INSTRUMENTATION", "TELECOM", "HVAC" }.Contains(d) => "E&I",
            "PIPING" => "PIPING",
            "EQUIPMENT" => "EQUIPMENT",
            "MECHANICAL COMPLETION" => "MC",
            _ => "OTHERS"
        },
        x.TOTAL,
        x.COMP,
        x.REM
    })
    .GroupBy(y => y.DISCIP)
    .Select(finalGroup => new 
    {
        DISCIP = finalGroup.Key,
        TOTAL = finalGroup.Sum(y => y.TOTAL),
        DONE = finalGroup.Sum(y => y.COMP), // 对应SQL的SUM(COMP) as DONE
        REM = finalGroup.Sum(y => y.REM)
    })
    .ToList();

逻辑匹配说明 #

• 子查询部分：LINQ的第一阶段disciplineStats完全对应SQL内层按Discipline分组统计的逻辑，用Count方法实现SQL中case分支的计数逻辑。
• 外层汇总：第二阶段通过switch表达式实现SQL的case类别映射，再二次分组汇总得到最终结果。

兼容低版本.NET的写法 #

如果你的项目不支持C# 8.0的switch表达式，可以替换为三元表达式：

DISCIP = 
    new[] { "ELECTRICAL", "F&G", "INSTRUMENTATION", "TELECOM", "HVAC" }.Contains(x.Discipline) ? "E&I" :
    x.Discipline == "PIPING" ? "PIPING" :
    x.Discipline == "EQUIPMENT" ? "EQUIPMENT" :
    x.Discipline == "MECHANICAL COMPLETION" ? "MC" :
    "OTHERS"

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