如何将带Row Number的SQL查询转换为EF Core Lambda表达式？

原SQL的核心需求是获取每个设备对应各维护周期的最新维护记录，计算下次维护工时，以下是适配EF Core 7.0的Lambda表达式实现：

修正后的控制器方法 #

[HttpGet]
public async Task<IEnumerable<NextMaintenance_ViewModel>> GetNextMaintenancesList()
{
    // 构建子查询：获取每个EquipmentId+PeriodId的最新维护记录
    var latestMaintenances = _context.Maintenances
        .Join(_context.MaintenancePeriods, 
              mn => mn.PeriodId, 
              mp => mp.Id, 
              (mn, mp) => new 
              {
                  mn.EquipmentId,
                  mn.PeriodId,
                  mn.MachineHour,
                  mn.MaintenanceDate,
                  mp.PeriodHour
              })
        .Select(m => new 
        {
            m.EquipmentId,
            m.PeriodId,
            m.MachineHour,
            m.MaintenanceDate,
            m.PeriodHour,
            // 开窗函数：按设备+周期分组，维护日期倒序生成行号
            RowNum = EF.Functions.RowNumber()
                .Over()
                .PartitionBy(m.EquipmentId, m.PeriodId)
                .OrderByDescending(m.MaintenanceDate)
        })
        .Where(m => m.RowNum == 1); // 只取每个分组的最新记录

    // 关联设备表并映射到ViewModel
    var result = await _context.Equipments
        .LeftJoin(latestMaintenances, 
                  e => e.Id, 
                  m => m.EquipmentId, 
                  (e, m) => new NextMaintenance_ViewModel
                  {
                      EquipmentId = e.Id,
                      Location = e.Location,
                      MachineHour = e.MachineHour,
                      PeriodHour = m?.PeriodHour,
                      MaintenanceMachineHour = m?.MachineHour,
                      NextMaintenance = m != null ? m.MachineHour + m.PeriodHour : null,
                      HoursLeft = m != null ? (m.MachineHour + m.PeriodHour) - e.MachineHour : null
                  })
        .Where(vm => vm.PeriodHour != null) // 过滤无有效周期的记录
        .OrderBy(vm => vm.Location)
        .ThenBy(vm => vm.PeriodHour)
        .ToListAsync();

    return result;
}

关键说明 #

• 开窗函数适配：EF Core 7.0原生支持EF.Functions.RowNumber()，完美对应原SQL的ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)逻辑，确保只取每个设备+周期的最新维护记录。
• Left Join逻辑：严格对齐原SQL的LEFT JOIN逻辑，保留设备数据的同时关联最新维护记录，后续通过Where(vm => vm.PeriodHour != null)过滤，和原SQL的M.PeriodHour IS NOT NULL条件一致。
• 空值处理：由于是Left Join，对维护记录相关字段做空值判断，避免空引用异常，同时符合业务逻辑。
• 异步优化：使用ToListAsync()替代同步的ToList()，适配异步控制器模式，提升接口性能。

实体关系校验 #

确保你的实体类已正确配置导航关系：

• Equipment实体包含ICollection<Maintenance>类型的Maintenances导航属性
• Maintenance实体包含MaintenancePeriod类型的Period导航属性（或通过外键正确关联）

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