求助：将指定SQL查询转换为含分组取最大值的LINQ查询

首先明确核心需求：实现与原SQL等价的LINQ逻辑——多表关联后按DeviceDataId分组，获取每组Head、Shoulder、Chest的最大值，适配所有BlastRecord_Id，并支持结果排序。

修正后的LINQ查询（查询语法） #

var myOverPressures = (from eop in db.EventUserOverPressures
                       join uei in ueiList on eop.UserEventInfo_Id equals uei.UserEventInfo_Id
                       join br in blastRecords on uei.BlastRecord_Id equals br.BlastRecord_Id
                       join wfl in weaponFiringLogss on br.BlastRecord_Id equals wfl.BlastRecord_Id
                       join wf in weaponsFired on wfl.Blast_WFL_Id equals wf.Blast_WFL_Id
                       where eop.Chest > 0 || eop.Head > 0 || eop.Shoulder > 0
                       group eop by eop.DeviceDataId into deviceGroup
                       select new
                       {
                           DeviceDataId = deviceGroup.Key,
                           MaxHead = deviceGroup.Max(x => x.Head),
                           MaxShoulder = deviceGroup.Max(x => x.Shoulder),
                           MaxChest = deviceGroup.Max(x => x.Chest)
                       })
                       .OrderByDescending(x => x.MaxChest)
                       .ThenByDescending(x => x.MaxHead)
                       .ToList();

关键细节说明 #

• 分组与聚合：通过group eop by eop.DeviceDataId into deviceGroup实现按设备ID分组，用Max()方法分别计算三个字段的最大值，完全对应原SQL的GROUP BY DeviceId + MAX()逻辑。
• 关联逻辑对齐：修正了WeaponsFiringLog的关联条件，和原SQL一致关联BlastRecord的BlastRecord_Id，避免数据关联错误。
• 动态适配BlastRecord_Id：去掉了原SQL中固定的WHERE br.BlastRecord_Id = 1599，如果需要过滤特定ID，可在where子句中追加&& br.BlastRecord_Id == 目标ID（目标ID可作为参数传入）。
• 排序配置：末尾的OrderByDescending/ThenByDescending支持多字段排序，可根据业务需求调整排序字段和升降序。

方法语法版本（可选） #

若习惯链式调用风格，可使用以下写法：

var myOverPressures = db.EventUserOverPressures
    .Join(ueiList, eop => eop.UserEventInfo_Id, uei => uei.UserEventInfo_Id, (eop, uei) => new { eop, uei })
    .Join(blastRecords, x => x.uei.BlastRecord_Id, br => br.BlastRecord_Id, (x, br) => new { x.eop, x.uei, br })
    .Join(weaponFiringLogss, x => x.br.BlastRecord_Id, wfl => wfl.BlastRecord_Id, (x, wfl) => new { x.eop, x.uei, x.br, wfl })
    .Join(weaponsFired, x => x.wfl.Blast_WFL_Id, wf => wf.Blast_WFL_Id, (x, wf) => x.eop)
    .Where(eop => eop.Chest > 0 || eop.Head > 0 || eop.Shoulder > 0)
    .GroupBy(eop => eop.DeviceDataId)
    .Select(group => new
    {
        DeviceDataId = group.Key,
        MaxHead = group.Max(e => e.Head),
        MaxShoulder = group.Max(e => e.Shoulder),
        MaxChest = group.Max(e => e.Chest)
    })
    .OrderByDescending(res => res.MaxChest)
    .ThenByDescending(res => res.MaxHead)
    .ToList();

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