EF Core Linq如何合并同一列多条件计数查询？

当然可以将两个查询合并为单次数据库查询，以下是两种常用的实现方式：

方式一：通过GroupBy分组统计 #

先过滤出目标ClientId及指定的ExceptionType，再按ExceptionType分组统计数量，最后提取对应类型的结果：

var exceptionCounts = await _dbRO.AllOpenExceptions
    .Where(x => x.ClientId == _user.ClientId && (x.ExceptionType == "Check" || x.ExceptionType == "ACH"))
    .GroupBy(x => x.ExceptionType)
    .Select(g => new { ExceptionType = g.Key, Count = g.Count() })
    .ToListAsync();

// 提取对应计数，无匹配时默认0
var checkTotal = exceptionCounts.FirstOrDefault(x => x.ExceptionType == "Check")?.Count ?? 0;
var achTotal = exceptionCounts.FirstOrDefault(x => x.ExceptionType == "ACH")?.Count ?? 0;

方式二：直接投影计算双条件计数（更高效） #

利用EF Core的聚合函数支持，直接在查询中同时计算两个条件的总数，仅需一次数据库交互：

var counts = await _dbRO.AllOpenExceptions
    .Where(x => x.ClientId == _user.ClientId)
    .Select(_ => new 
    {
        CheckTotal = _dbRO.AllOpenExceptions.Count(e => e.ClientId == _user.ClientId && e.ExceptionType == "Check"),
        AchTotal = _dbRO.AllOpenExceptions.Count(e => e.ClientId == _user.ClientId && e.ExceptionType == "ACH")
    })
    .FirstOrDefaultAsync();

var checkTotal = counts?.CheckTotal ?? 0;
var achTotal = counts?.AchTotal ?? 0;

或者使用Sum结合条件判断的写法，语义更直观：

var counts = await _dbRO.AllOpenExceptions
    .Where(x => x.ClientId == _user.ClientId)
    .Select(x => new 
    {
        CheckFlag = x.ExceptionType == "Check" ? 1 : 0,
        AchFlag = x.ExceptionType == "ACH" ? 1 : 0
    })
    .GroupBy(_ => 1)
    .Select(g => new 
    {
        CheckTotal = g.Sum(x => x.CheckFlag),
        AchTotal = g.Sum(x => x.AchFlag)
    })
    .FirstOrDefaultAsync();

var checkTotal = counts?.CheckTotal ?? 0;
var achTotal = counts?.AchTotal ?? 0;

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