多表、条件判断及聚合分组的SQL转LINQ Lambda等价实现问询

基于给定的数据表结构，与目标SQL语句等价的LINQ Lambda表达式如下（假设使用Entity Framework，上下文对象为db）：

var result = db.UnitAlertStatus
    .Join(db.Unit, 
          uas => uas.AlertedUnitID, 
          u => u.ID, 
          (uas, u) => new { uas, u })
    .Join(db.UnitType, 
          combined => combined.u.UnitTypeID, 
          ut => ut.ID, 
          (combined, ut) => new { combined.uas, combined.u, ut })
    .Where(x => x.uas.EpochStamp > 1698150366500)
    .GroupBy(x => new 
    {
        x.uas.AlertedUnitID,
        Unit_HardwareID = x.u.HardwareID,
        Unit_Name = x.u.Name,
        UnitType_Name = x.ut.Name
    })
    .Select(g => new 
    {
        g.Key.AlertedUnitID,
        Count_Caution = g.Count(item => item.uas.UnitAlertStatus == 1),
        Count_Alarm = g.Count(item => item.uas.UnitAlertStatus == 3),
        Count_Critical = g.Count(item => item.uas.UnitAlertStatus == 7),
        g.Key.Unit_HardwareID,
        g.Key.Unit_Name,
        g.Key.UnitType_Name
    })
    .ToList();

说明 #

• 两次Join操作对应原SQL中三个表的关联逻辑，分别关联UnitAlertStatus与Unit、Unit与UnitType
• Where子句过滤时间戳大于指定值的记录
• GroupBy按原SQL要求的字段分组
• Select中通过Count结合条件判断，实现原SQL中Count(case...)的统计逻辑

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