C# LINQ多级排序：如何为嵌套Tags列表添加Tag.Title为第三排序条件

报错原因 #

1. Tags属于集合类型，你直接访问x.Tags.Title是从集合直接访问单个元素属性，EF Core无法确定要使用集合中哪个Tag的Title作为排序依据，因此无法翻译成SQL语句
2. 直接对投影后的嵌套集合属性做跨层级排序，部分低版本EF Core不支持该类逻辑的服务端翻译

方案1：服务端可翻译排序（性能优先推荐） #

先明确第三排序规则：单个ExamContent对应多个Tag，需指定排序用的Tag取值逻辑，比如取排序后的首个Tag的Title作为排序依据，修改后代码如下：

var examContents = await this._context.ExamContents
    .AsNoTracking()
    .Where(x => x.IsNewVersion && versionIds.Contains(x.VersionId))
    .Select(x => new ExamContentModel
    {
       Id = x.Id,
       VersionId = x.VersionId,
       LearningOutcomeId = x.LearningOutcomeId,
       SectionId = x.SectionId,
       Title = x.LearningOutcome.Title,
       AssignedQuestionCount = x.AssignedQuestionCount,
       DisplayOrder=x.DisplayOrder,
       IsNewVersion=x.IsNewVersion,
       Questions = x.LearningOutcome.Questions.Where(x => x.Category == QuestionCategory.Certification).Select(y => new Question
       {
           Title = y.Title,
           Category = y.Category,
           Status = y.Status,
           IsDraft = y.IsDraft,
           PublishedQuestionId = y.PublishedQuestionId,
           ExamVersionId = y.ExamVersionId
       }),
       // 如需给Tags集合内部排序，可直接在投影后加排序规则，EF Core 3.0+支持翻译
       Tags = x.LearningOutcome.LearningOutcomeTags.Select(t => new Tag
       {
           Id = t.TagId,
           Title = t.Tag.Title,
           Type = t.Tag.Type,
           IsRemovedFromLO = t.IsUntaggedFromLO
       }).OrderBy(t => t.Type).ThenBy(t => t.Title)
    })
    .OrderBy(x=>x.SectionId)
    .ThenBy(x=>x.DisplayOrder)
    // 新增第三排序规则：取首个Tag的Title排序，空值处理逻辑可自行调整
    .ThenBy(x => x.Tags.OrderBy(t => t.Title).FirstOrDefault() != null 
        ? x.Tags.OrderBy(t => t.Title).FirstOrDefault().Title 
        : string.Empty)
    .ToListAsync();

方案2：客户端排序（兼容性优先推荐） #

如果数据量不大、或者排序规则复杂无法翻译成SQL，可以先把数据拉到内存再做排序，完全避免翻译问题：

// 先拉取全量数据到内存
var rawExamContents = await this._context.ExamContents
    .AsNoTracking()
    .Where(x => x.IsNewVersion && versionIds.Contains(x.VersionId))
    .Select(x => new ExamContentModel
    {
       Id = x.Id,
       VersionId = x.VersionId,
       LearningOutcomeId = x.LearningOutcomeId,
       SectionId = x.SectionId,
       Title = x.LearningOutcome.Title,
       AssignedQuestionCount = x.AssignedQuestionCount,
       DisplayOrder=x.DisplayOrder,
       IsNewVersion=x.IsNewVersion,
       Questions = x.LearningOutcome.Questions.Where(x => x.Category == QuestionCategory.Certification).Select(y => new Question
       {
           Title = y.Title,
           Category = y.Category,
           Status = y.Status,
           IsDraft = y.IsDraft,
           PublishedQuestionId = y.PublishedQuestionId,
           ExamVersionId = y.ExamVersionId
       }),
       Tags = x.LearningOutcome.LearningOutcomeTags.Select(t => new Tag
       {
           Id = t.TagId,
           Title = t.Tag.Title,
           Type = t.Tag.Type,
           IsRemovedFromLO = t.IsUntaggedFromLO
       })
    })
    .ToListAsync();

// 内存中完成自定义排序，支持任意复杂规则
var examContents = rawExamContents
    .OrderBy(x => x.SectionId)
    .ThenBy(x => x.DisplayOrder)
    // 示例：按所有Tag的Title拼接后排序，可自行修改规则
    .ThenBy(x => string.Join(",", x.Tags.OrderBy(t => t.Title).Select(t => t.Title)))
    .ToList();

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