为带集合属性的类编写Linq查询时遇EfCore5转换错误

问题原因 #

该报错属于EF Core 5的固有特性限制：

1. EF Core 5对投影中的嵌套集合子查询转换支持度极低，父查询使用Distinct操作后未携带足够键信息用于关联子查询，直接触发转换失败
2. 你的代码最后一步在IQueryable上下文中直接调用.ToList()为ArticleTexts属性赋值，该操作无法被转换为SQL语句

方案一：先加载扁平数据到内存再分组（兼容EF Core 5，最稳妥） #

该方案绕开EF Core的查询转换限制，适合绝大多数数据量不大的业务场景：

private async Task<List<AgregatedArticle>> GetLawArticlesGroupedById(DbSet<LawArticleDetail> dbSet, string userContentLangRestriction = null)
{
    var dbContext = await GetDbContextAsync();

    // 第一步：查询所有需要的扁平数据，全部加载到内存
    var flatArticles = await
        (from article in dbSet
        join askedL in dbContext.Langs
            .Where(l => l.LanguageCode == userContentLangRestriction)
            on article.LanguageCode equals askedL.StringValue
            into askedLanguages
        from askedLawLanguage in askedLanguages.DefaultIfEmpty()
        join fallbackL in dbContext.Langs
            .Where(l => l.LanguageCode == CoreConstants.LanguageCodes.English)
        on article.LanguageCode equals fallbackL.StringValue
        into fallbackLanguages
        from fallbackLanguage in fallbackLanguages.DefaultIfEmpty()
        select new
        {
            article.ArticleId,
            article.ArticleText,
            LanguageName = askedLawLanguage.ShortName ?? fallbackLanguage.ShortName
        })
        .OrderBy(x => x.ArticleId)
        .ThenBy(x => x.LanguageName)
        .ThenBy(x => x.ArticleText)
        .ToListAsync();

    // 第二步：内存中按ArticleId分组生成聚合结果
    var aggregatedArticles = flatArticles
        .GroupBy(x => x.ArticleId)
        .Select(g => new AgregatedArticle
        {
            ArticleId = g.Key,
            ArticleTexts = g.Select(x => new AgregatedArticle.Data
            {
                ArticleText = x.ArticleText,
                LanguageName = x.LanguageName
            }).ToList()
        })
        .ToList();

    // 若必须返回IQueryable类型，可改为 return aggregatedArticles.AsQueryable();
    return aggregatedArticles;
}

方案二：升级EF Core版本获取原生支持 #

如果需要保留IQueryable的延迟加载特性，可升级到EF Core 6及以上版本，该版本大幅优化了嵌套集合投影的转换支持，调整后的写法如下：

private async Task<IQueryable<AgregatedArticle>> GetLawArticlesGroupedById(DbSet<LawArticleDetail> dbSet, string userContentLangRestriction = null)
{
    var dbContext = await GetDbContextAsync();

    return dbSet
        .GroupBy(x => x.ArticleId)
        .Select(g => new AgregatedArticle
        {
            ArticleId = g.Key,
            ArticleTexts = g.Select(x => new AgregatedArticle.Data
            {
                ArticleText = x.ArticleText,
                LanguageName = dbContext.Langs
                    .Where(l => l.LanguageCode == x.LanguageCode && l.LanguageCode == userContentLangRestriction)
                    .Select(l => l.ShortName)
                    .FirstOrDefault() 
                    ?? dbContext.Langs
                    .Where(l => l.LanguageCode == CoreConstants.LanguageCodes.English && l.LanguageCode == x.LanguageCode)
                    .Select(l => l.ShortName)
                    .FirstOrDefault()
            }).ToList()
        });
}

注意事项 #

• 若无法升级EF Core版本，优先使用方案一，不存在任何查询转换问题
• 原有代码中冗余的两次关联查询ArticleId的逻辑可直接删除，直接查询全量数据再分组性能更优
• 不要在IQueryable的投影中使用关联外部IQueryable的子查询，极易触发转换失败

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