如何转换SQL为LINQ实现按BookingId分组取最新状态记录

原逻辑错误点 #

你之前的两种写法存在两个核心问题：

• 初始LINQ写法逻辑顺序颠倒：先筛选StatusId=3再分组取最大Id，实际查询的是「每个预订所有状态为3的记录中最新的一条」，而原SQL逻辑是「每个预订最新的一条状态记录，且状态为3」，两者逻辑完全不匹配，因此会返回多余结果。
• 全量拉取数据到内存处理的方案存在严重性能缺陷：会将整张BookingStatus表的数据全部加载到应用内存，数据量增长后会产生极高的数据库IO和内存占用，完全可以将计算逻辑下推到数据库侧执行。

高性能实现方案（数据库侧执行） #

以下写法兼容EF Core所有版本、EF6，会被翻译为标准SQL在数据库端完成所有计算，仅返回最终符合条件的结果，性能和你手写的原生CTE SQL一致：

var query = from bs in _context.Set<BookingStatus>()
    // 子查询定位每个BookingId对应的最新状态记录Id
    join latestStatus in (
        from s in _context.Set<BookingStatus>()
        group s by s.BookingId into g
        select new 
        {
            BookingId = g.Key,
            LatestRecordId = g.Max(x => x.Id)
        }
    ) on new { bs.BookingId, bs.Id } equals new { latestStatus.BookingId, Id = latestStatus.LatestRecordId }
    // 筛选最新状态为3的记录
    where bs.StatusId == 3
    select bs;

// 执行查询，仅返回符合条件的结果
var result = await query.ToListAsync();

优化建议 #

• 为BookingStatus表建立(BookingId, Id)的联合索引，可以让上述查询的执行效率达到最优，即使表数据量达到百万级也可以毫秒级返回结果。
• 如果你使用的是EF Core 6及以上版本，也可以直接使用内置的RowNumber函数直接映射原SQL的窗口函数写法，EF会自动翻译为和你原CTE完全一致的SQL：

var query = _context.Set<BookingStatus>()
    .Select(bs => new 
    {
        Record = bs,
        Rnk = EF.Functions.RowNumber(
            EF.PartitionBy(bs.BookingId),
            EF.OrderByDescending(bs.Id)
        )
    })
    .Where(x => x.Rnk == 1 && x.Record.StatusId == 3)
    .Select(x => x.Record);

var result = await query.ToListAsync();

内容的提问来源于stack exchange，提问作者team-btg