如何在LINQ表达式中实现SQL的DISTINCT ON功能？

需求 #

• 仅查询types表中激活状态（is_active = true）的任务
• 左连接types与batches表：新创建的任务无执行历史时，BatchId为null但JobId需保留有效值
• 每个任务仅保留batches表中对应Id最大的一条记录

原SQL查询 #

select distinct on (b.type_id)
t.id as "JobId", b.id , t.interval_minutes 
from types t 
left join batches b on t.id = b.type_id 
where t.is_active 
order by t.id , b.id desc

LINQ实现方案 #

方案一：使用GroupJoin（推荐，贴近左连接逻辑） #

var query = _context.Types
    // 过滤激活状态的任务
    .Where(t => t.IsActive)
    // 左连接batches表，按任务ID分组关联对应的执行记录
    .GroupJoin(
        _context.Batches,
        type => type.Id,
        batch => batch.TypeId,
        (type, relatedBatches) => new 
        {
            type.Id,
            type.IntervalMinutes,
            // 取当前任务对应的最大ID的执行记录，无记录则为null
            LatestBatch = relatedBatches.OrderByDescending(b => b.Id).FirstOrDefault()
        })
    // 映射到目标输出结构
    .Select(result => new 
    {
        JobId = result.Id,
        BatchId = result.LatestBatch?.Id,
        result.IntervalMinutes
    });

// 执行查询（示例为异步方式）
var finalResult = await query.ToListAsync();

方案二：使用SelectMany + DefaultIfEmpty #

var query = _context.Types
    .Where(t => t.IsActive)
    // 对每个任务，关联其最大ID的执行记录，无记录则返回null（左连接）
    .SelectMany(t => _context.Batches
        .Where(b => b.TypeId == t.Id)
        .OrderByDescending(b => b.Id)
        .Take(1)
        .DefaultIfEmpty(),
        (t, batch) => new 
        {
            JobId = t.Id,
            BatchId = batch?.Id,
            t.IntervalMinutes
        });

var finalResult = await query.ToListAsync();

问题说明 #

之前使用GroupBy时出现报错，是因为直接按BatchId分组会遇到null值的分组键问题。上述两种方案通过先关联任务对应的所有执行记录，再在每组内筛选最大ID的记录，避免了null分组的异常，同时能生成等价于原SQL的查询逻辑（EF Core会根据数据库类型优化生成对应的SQL，PostgreSQL环境下大概率会生成DISTINCT ON或等价的子查询）。

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