如何通过EF Core查询返回每种类型各一条数据

问题背景 #

需要查询某客户每种类型的最新笔记各一条，并按时间降序排列。对应的SQL Server查询可以正常运行：

SELECT  
    note_id, st_id, note_type, signature_date 
FROM 
    (SELECT
         note_id, st_id, note_type, signature_date,
         ROW_NUMBER() OVER (PARTITION BY note_type ORDER BY signature_date DESC) AS row_num
     FROM 
         notes_log
     WHERE 
         cl_id = 1
         AND svce_type = 'service type 1'
         AND (note_type = 'note type 1' OR note_type = 'note type 2' OR note_type = 'note type 3')
     ) AS subquery
WHERE row_num = 1
ORDER BY signature_date DESC;

但转换为Linq时出现转译错误，尝试的两种写法均失败：

尝试1：GroupBy + SelectMany #

var lastNotes = await dbContext.Set<NotesLog>()
            .Where(s => s.ClientId == id && s.ServiceType == serviceType && (s.NoteType == "type 1" || s.NoteType == "type 2" || s.NoteType == "type 3"))
            .OrderByDescending(s => s.Signaturedate)
            .GroupBy(s => s.NoteType)
            .SelectMany(group => group.Take(1))
            .Select(s => new
            {
                s.NoteId,
                s.StateId,
                s.NoteType,
                s.Signaturedate
            })
            .ToListAsync();

尝试2：DistinctBy #

用.DistinctBy(s => s.NoteType)替代上述GroupBy和SelectMany代码段，同样返回错误：

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

用户希望避免客户端筛选，找到能正确转译为SQL、返回结果按时间降序排列的方案。

解决方案 #

直接在Linq中模拟SQL的窗口函数逻辑，通过RowNumber()分区排序后取第一条，最后再整体排序：

var lastNotes = await dbContext.Set<NotesLog>()
    .Where(s => s.ClientId == id && s.ServiceType == serviceType 
                && new[] { "type 1", "type 2", "type 3" }.Contains(s.NoteType))
    .Select(s => new 
    {
        s.NoteId,
        s.StateId,
        s.NoteType,
        s.Signaturedate,
        RowNum = EF.Functions.RowNumber()
            .Over()
            .PartitionBy(s.NoteType)
            .OrderByDescending(s.Signaturedate)
    })
    .Where(x => x.RowNum == 1)
    .OrderByDescending(x => x.Signaturedate)
    .Select(x => new 
    {
        x.NoteId,
        x.StateId,
        x.NoteType,
        x.Signaturedate
    })
    .ToListAsync();

说明 #

• 使用EF.Functions.RowNumber()模拟SQL的ROW_NUMBER()窗口函数，按NoteType分区，每个分区内按Signaturedate降序生成行号
• 过滤行号为1的记录，即每个类型的最新笔记
• 最后对结果按Signaturedate降序排列，满足整体排序需求
• 用Contains替代多个OR，让代码更简洁且同样能被EF Core正确转译为SQL

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