如何优化LINQ表达式翻译报错修复方案？避免多次SQL调用

我需要对文档信息表做自左连接，获取属于特定社保号（SSN）且页数最高的唯一文档。首次尝试时LINQ表达式无法被EF Core翻译，虽然每步调用ToList()能解决，但会多次触发SQL查询，希望全程用IQueryable构建，仅最后执行一次SQL。

首次尝试代码 #

// 获取包含指定社保号的Doc或eDoc类型文档
var documents = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
   .Where(s => listOfSSNs.Contains(s.PropId)
    && s.StrVal == SSNparam && (s.DocumentType == "Doc" ||
   s.DocumentType == "eDoc"));

// 统计每个文档的页数
var queryCount = documents.GroupBy(x => new { x.Tocid, x.PropId })
             .Select(x => new
             {
                 Tocid = x.Key.Tocid,
                 PropId = x.Key.PropId,
                 PageNum = x.Count()
             });

// 去重（避免同一文档因PropId重复出现）
var queryDistinct = queryCount.DistinctBy(x => x.Tocid);

// 左连接原文档表获取完整信息
var queryCombine =
            (from document in documents
             join qd in queryDistinct on document.Tocid equals qd.Tocid into gj
             from subset in gj.DefaultIfEmpty()
             select new DocListFullPathWithTagsLimitedVw
             {
                 DocumentName = document.DocumentName,
                 Tocid = document.Tocid,
                 EdocStoreid = document.EdocStoreid,
                 Storeid = document.Storeid,
                 EdocExt = document.EdocExt,
                 PropId = document.PropId,
                 StrVal = document.StrVal,
                 FullPathAndFilename = document.FullPathAndFilename,
                 DocumentType = document.DocumentType,
                 VolName = document.VolName,
                 Modified = document.Modified,
                 Created = document.Created,
                 PageNum = subset.PageNum
             }).ToList();

return queryCombine;

报错信息 #

[LaserficheDocFinder] : System.InvalidOperationException: The LINQ expression 'DbSet<DocListFullPathWithTagsLimitedVw>()
.Where(s => __listOfSSNs_0
    .Contains(s.PropId) && s.StrVal == __SSNparam_1 && s.DocumentType == "Doc" || s.DocumentType == "eDoc")
.GroupBy(x => new { 
    Tocid = x.Tocid, 
    PropId = x.PropId
 })
.Select(x => new { 
    Tocid = x.Key.Tocid, 
    PropId = x.Key.PropId, 
    PageNum = x
        .AsQueryable()
        .Count()
 })
.DistinctBy(x => x.Tocid)' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

临时解决方案（多次触发SQL，非最优） #

var documents = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
               .Where(s => listOfSSNs.Contains(s.PropId)
                && s.StrVal == SSNparam && (s.DocumentType == "Doc" ||
               s.DocumentType == "eDoc" )).ToList();

// 统计每个文档的页数
var queryCount = documents.GroupBy(x => new { x.Tocid, x.PropId })
             .Select(x => new
             {
                 Tocid = x.Key.Tocid,
                 PropId = x.Key.PropId,
                 PageNum = x.Count()
             }).ToList();

// 去重（避免同一文档因PropId重复出现）
var queryDistinct = queryCount.DistinctBy(x => x.Tocid);

var queryDistinctList = queryDistinct.AsEnumerable().ToList();

var queryCombine =
            (from document in documents
             join qd in queryDistinctList on document.Tocid equals qd.Tocid into gj
             from subset in gj.DefaultIfEmpty()
             where (document.DocumentType == "Doc" && document.PageNum == subset.PageNum && document.PropId == subset.PropId)
             || (document.DocumentType == "eDoc" && document.PageNum == null && subset.PageNum == 1 && document.PropId == subset.PropId )
             || (document.DocumentType == "eDoc" && document.PageNum != null && document.PageNum == subset.PageNum && document.PropId == subset.PropId)
             || (document.FullPathAndFilename == null && document.PageNum == null && subset.PageNum == 1 && document.PropId == subset.PropId)
             select new DocListFullPathWithTagsLimitedVw
             {
                 DocumentName = document.DocumentName,
                 Tocid = document.Tocid,
                 EdocStoreid = document.EdocStoreid,
                 Storeid = document.Storeid,
                 EdocExt = document.EdocExt,
                 PropId = document.PropId,
                 StrVal = document.StrVal,
                 FullPathAndFilename = document.FullPathAndFilename,
                 DocumentType = document.DocumentType,
                 VolName = document.VolName,
                 Modified = document.Modified,
                 Created = document.Created,
                 PageNum = subset.PageNum
             }).ToList();

return queryCombine;

优化方案（全程IQueryable，单次SQL查询） #

问题核心是DistinctBy无法被EF Core正确翻译，且分组关联逻辑可调整。以下两种方案均全程基于IQueryable构建，仅最后ToList()触发一次SQL查询：

方案1：使用窗口函数（推荐，高效） #

利用EF Core 5+支持的ROW_NUMBER()窗口函数，按Tocid分组后按页数倒序排序，直接取每组第一条（即页数最高的记录）：

var query = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
    .Where(s => listOfSSNs.Contains(s.PropId) 
        && s.StrVal == SSNparam 
        && (s.DocumentType == "Doc" || s.DocumentType == "eDoc"))
    // 为每个Tocid生成行号，按PageNum倒序（eDoc的PageNum为null时视为1页）
    .Select(s => new 
    {
        Doc = s,
        RowNum = EF.Functions.RowNumber()
            .Over(
                PartitionBy: s.Tocid,
                OrderBy: s.PageNum.HasValue ? s.PageNum : 1 descending
            )
    })
    // 取每个Tocid的第一条（页数最高的记录）
    .Where(x => x.RowNum == 1)
    // 映射到目标实体
    .Select(x => new DocListFullPathWithTagsLimitedVw
    {
        DocumentName = x.Doc.DocumentName,
        Tocid = x.Doc.Tocid,
        EdocStoreid = x.Doc.EdocStoreid,
        Storeid = x.Doc.Storeid,
        EdocExt = x.Doc.EdocExt,
        PropId = x.Doc.PropId,
        StrVal = x.Doc.StrVal,
        FullPathAndFilename = x.Doc.FullPathAndFilename,
        DocumentType = x.Doc.DocumentType,
        VolName = x.Doc.VolName,
        Modified = x.Doc.Modified,
        Created = x.Doc.Created,
        PageNum = x.Doc.PageNum ?? 1
    })
    // 最后执行SQL
    .ToList();

return query;

方案2：分组取最大值再关联（兼容低版本EF Core） #

先获取每个Tocid对应的最高页数，再关联原表匹配记录：

// 第一步：获取每个Tocid的最高页数（处理eDoc的null情况）
var maxPagePerToc = db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
    .Where(s => listOfSSNs.Contains(s.PropId) 
        && s.StrVal == SSNparam 
        && (s.DocumentType == "Doc" || s.DocumentType == "eDoc"))
    .GroupBy(s => s.Tocid)
    .Select(g => new 
    {
        Tocid = g.Key,
        MaxPage = g.Max(s => s.PageNum ?? 1)
    });

// 第二步：关联原表，匹配对应最高页数的文档
var query = from doc in db.DocListFullPathWithTagsLimitedVws.AsNoTracking()
            join maxPage in maxPagePerToc on doc.Tocid equals maxPage.Tocid
            where listOfSSNs.Contains(doc.PropId) 
                && doc.StrVal == SSNparam 
                && (doc.DocumentType == "Doc" || doc.DocumentType == "eDoc")
                // 匹配页数：Doc直接匹配数值，eDoc处理null情况
                && ((doc.DocumentType == "Doc" && doc.PageNum == maxPage.MaxPage)
                    || (doc.DocumentType == "eDoc" && (doc.PageNum ?? 1) == maxPage.MaxPage))
            select new DocListFullPathWithTagsLimitedVw
            {
                DocumentName = doc.DocumentName,
                Tocid = doc.Tocid,
                EdocStoreid = doc.EdocStoreid,
                Storeid = doc.Storeid,
                EdocExt = doc.EdocExt,
                PropId = doc.PropId,
                StrVal = doc.StrVal,
                FullPathAndFilename = doc.FullPathAndFilename,
                DocumentType = doc.DocumentType,
                VolName = doc.VolName,
                Modified = doc.Modified,
                Created = doc.Created,
                PageNum = maxPage.MaxPage
            };

// 最后执行SQL
return query.ToList();

方案说明 #

• 窗口函数方案仅需一次表扫描，生成的SQL效率更高；
• 低版本EF Core可采用分组取最大值再关联的方式，同样全程基于IQueryable，避免客户端查询；
• 两种方案均规避了无法翻译的DistinctBy调用，所有逻辑均可被EF Core转化为SQL语句。

内容的提问来源于stack exchange，提问作者It's Just a Printer Driver