如何将指定SQL语句转换为匹配给定C#类的LINQ查询语句

前置准备 #

我们先将时间边界计算放在查询逻辑外，既保证EF Core可以正常翻译为SQL参数，也和原SQL的UTC时间逻辑完全对齐：

// 取当前UTC时间，对齐SQL的GETUTCDATE()
var nowUtc = DateTimeOffset.UtcNow;
// UTC今日开始时间（零点）
var todayStart = nowUtc.Date;
// UTC今日结束时间（次日零点）
var todayEnd = todayStart.AddDays(1);
// UTC当前小时的开始时间
var currentHourStart = new DateTimeOffset(nowUtc.Year, nowUtc.Month, nowUtc.Day, nowUtc.Hour, 0, 0, TimeSpan.Zero);
// UTC当前小时的结束时间（下一小时整点）
var currentHourEnd = currentHourStart.AddHours(1);

完整查询代码（与原SQL INNER JOIN逻辑完全一致） #

var statisticsList = (
    from monitor in MyDbContext.Monitors
    // 过滤活跃监控项，对应SQL WHERE is_active = 1
    where monitor.is_active
    // 关联计数器表，对应SQL INNER JOIN CollectorCounters
    join counter in MyDbContext.Counters
        on monitor.monitor_name equals counter.monitor_name
    // 按监控名称分组，对应SQL GROUP BY monitor_name
    group counter by monitor.monitor_name into monitorGroup
    select new AllStatistics
    {
        monitor_name = monitorGroup.Key,
        // 所有时间求和，无数据时自动返回0，对应ISNULL(SUM(count), 0)
        alltime = monitorGroup.Sum(c => c.count),
        // 仅统计今日UTC区间内的计数，对应原SQL的CASE WHEN逻辑
        today = monitorGroup.Sum(c => c.time_stamp >= todayStart && c.time_stamp < todayEnd ? c.count : 0),
        // 仅统计当前UTC小时内的计数，对应原SQL的CASE WHEN逻辑
        current_hour = monitorGroup.Sum(c => c.time_stamp >= currentHourStart && c.time_stamp < currentHourEnd ? c.count : 0)
    }).ToList();

补充说明 #

1. 原SQL是INNER JOIN，所以上面的查询只会返回有对应计数器数据的活跃监控项。如果需要返回所有活跃监控项（即使没有计数器数据也返回全0统计值），可以改用左关联写法：

var statisticsList = (
    from monitor in MyDbContext.Monitors
    where monitor.is_active
    join counter in MyDbContext.Counters
        on monitor.monitor_name equals counter.monitor_name into counterGroup
    from counter in counterGroup.DefaultIfEmpty()
    group counter by monitor.monitor_name into monitorGroup
    select new AllStatistics
    {
        monitor_name = monitorGroup.Key,
        alltime = monitorGroup.Sum(c => c == null ? 0 : c.count),
        today = monitorGroup.Sum(c => c != null && c.time_stamp >= todayStart && c.time_stamp < todayEnd ? c.count : 0),
        current_hour = monitorGroup.Sum(c => c != null && c.time_stamp >= currentHourStart && c.time_stamp < currentHourEnd ? c.count : 0)
    }).ToList();

2. 所有聚合逻辑中三元运算符的默认值为0，求和后自然对应原SQL的ISNULL(..., 0)逻辑，不需要额外处理空值。

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