将含SUM/CASE WHEN/GROUP BY的SQL查询转换为C# LINQ

首先明确目标SQL的核心逻辑（假设你的目标查询如下）：

SELECT
    CAST(ApplicationDate AS DATE) AS ApplicationDate,
    COUNT(*) AS TotalCount,
    SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) AS PendingCount,
    SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS ApprovedCount,
    SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) AS RejectedCount
FROM Client_Heatings
WHERE ApplicationDate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY CAST(ApplicationDate AS DATE)
ORDER BY ApplicationDate

以下是对应的LINQ实现，分两种常用场景：

EF Core/LINQ to Entities（直接转换为SQL查询） #

这种写法会被EF Core正确解析为等价SQL，适合直接操作数据库：

var oneYearAgo = DateTime.Now.AddYears(-1);

var stats = await _context.Client_Heatings
    // 过滤近一年的数据
    .Where(ch => ch.ApplicationDate >= oneYearAgo)
    // 按日期（去除时分秒部分）分组
    .GroupBy(ch => ch.ApplicationDate.Date)
    // 投影统计结果
    .Select(g => new 
    {
        ApplicationDate = g.Key,
        TotalCount = g.Count(),
        // 等价于SQL的SUM(CASE WHEN Status=0 THEN 1 ELSE 0 END)
        PendingCount = g.Sum(ch => ch.Status == 0 ? 1 : 0),
        ApprovedCount = g.Sum(ch => ch.Status == 1 ? 1 : 0),
        RejectedCount = g.Sum(ch => ch.Status == 2 ? 1 : 0)
    })
    .OrderBy(result => result.ApplicationDate)
    .ToListAsync();

LINQ to Objects（处理内存集合） #

如果是操作已加载到内存的数据集，可用更简洁的Count(predicate)替代Sum，效果一致：

var oneYearAgo = DateTime.Now.AddYears(-1);

// 假设clientHeatings是已加载到内存的Client_Heatings集合
var stats = clientHeatings
    .Where(ch => ch.ApplicationDate >= oneYearAgo)
    .GroupBy(ch => ch.ApplicationDate.Date)
    .Select(g => new 
    {
        ApplicationDate = g.Key,
        TotalCount = g.Count(),
        PendingCount = g.Count(ch => ch.Status == 0),
        ApprovedCount = g.Count(ch => ch.Status == 1),
        RejectedCount = g.Count(ch => ch.Status == 2)
    })
    .OrderBy(result => result.ApplicationDate)
    .ToList();

关键细节说明 #

• 分组时使用ApplicationDate.Date对应SQL的CAST(ApplicationDate AS DATE)，确保按纯日期而非完整时间戳分组
• LINQ中的Sum(condition ? 1 : 0)完全匹配SQL的SUM(CASE WHEN ... THEN 1 ELSE 0 END)逻辑，EF Core会自动转换为对应SQL语法
• 内存集合场景下，Count(predicate)是更高效简洁的写法，本质是统计满足条件的元素数量，与Sum结果完全一致

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