如何将日期分组统计的SQL查询转换为C# LINQ查询？

需求说明 #

对Client_Heatings表执行以下统计操作：

• 筛选近一年的数据
• 按ApplicationDate的日期部分分组
• 每组统计总记录数、各状态（Pending=0、Approved=1、Denied=2）的数量，同时返回分组对应的日期

原始SQL查询 #

SELECT 
        Count(he.ApplicationDate) AS Total,
        MAX(he.ApplicationDate) AS ApplicationDate,
        SUM(CASE WHEN he.Status=0 then 1 else 0 end )AS Last12MonthTotalPending,
        SUM(CASE WHEN he.Status = 1 THEN 1 ELSE 0 END )AS Last12MonthTotalApproved,
        SUM(CASE WHEN he.Status = 2 THEN 1 ELSE 0 END )AS Last12MonthTotalDenied
    FROM dbo.Client_Heatings he
    WHERE he.ApplicationDate >= DATEADD(Year,-1,GETDATE()) AND he.ApplicationDate <= GETDATE()
    GROUP BY CAST(ApplicationDate AS DATE)

C# LINQ实现 #

基础版本（直接使用状态数值） #

// 计算近一年的起始日期
DateTime oneYearAgo = DateTime.Now.AddYears(-1);

var statsQuery = dbContext.Client_Heatings
    // 筛选近一年的数据
    .Where(he => he.ApplicationDate >= oneYearAgo && he.ApplicationDate <= DateTime.Now)
    // 按ApplicationDate的日期部分分组（对应SQL的CAST(ApplicationDate AS DATE)）
    .GroupBy(he => he.ApplicationDate.Date)
    // 投影统计结果
    .Select(group => new
    {
        Total = group.Count(),
        ApplicationDate = group.Max(he => he.ApplicationDate),
        Last12MonthTotalPending = group.Count(he => he.Status == 0),
        Last12MonthTotalApproved = group.Count(he => he.Status == 1),
        Last12MonthTotalDenied = group.Count(he => he.Status == 2)
    })
    // 可选：按日期排序
    .OrderBy(result => result.ApplicationDate);

// 执行查询并获取结果
var statsResult = statsQuery.ToList();

枚举版本（更易读，推荐使用） #

如果Status字段对应C#枚举，先定义枚举：

public enum HeatingStatus
{
    Pending = 0,
    Approved = 1,
    Denied = 2
}

对应的LINQ查询：

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

var statsQuery = dbContext.Client_Heatings
    .Where(he => he.ApplicationDate >= oneYearAgo && he.ApplicationDate <= DateTime.Now)
    .GroupBy(he => he.ApplicationDate.Date)
    .Select(group => new
    {
        Total = group.Count(),
        ApplicationDate = group.Max(he => he.ApplicationDate),
        Last12MonthTotalPending = group.Count(he => he.Status == HeatingStatus.Pending),
        Last12MonthTotalApproved = group.Count(he => he.Status == HeatingStatus.Approved),
        Last12MonthTotalDenied = group.Count(he => he.Status == HeatingStatus.Denied)
    })
    .OrderBy(result => result.ApplicationDate);

var statsResult = statsQuery.ToList();

示例数据验证 #

针对提供的示例数据：

执行查询后会得到以下结果：

• 第一组（日期：1/1/2012）：Total=3，Pending=1，Approved=1，Denied=1
• 第二组（日期：2/1/2012）：Total=1，Pending=1，Approved=0，Denied=0

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