linq - Cannot compose correct Linq query for a class having collection properties - getting EfCore5 translation errors

Question

I have flat collection of the following data (IQueryable<Article>) which was obtained by querying DB:

ArticleId	LanguageName	ArticleText	ExtraData1	ExtraData2
1	English	EngText1	Something1	Something2
1	English	EngText2	Another1	Another2
1	French	FraText1	Blabla1
2	English	EngText2	Ololo1	Blabla2
2	German	GerText1		Naturlisch2

Now I need to fill the IQueryable<AgregatedArticle>: the idea is grouping by ArticleId and putting repeating data into nested list:

public class AgregatedArticle {
    public int ArticleId { get; set; }
    public List<Data> ArticleTexts { get; set; }

    public class Data {
        public string LanguageName { get; set; }
        public string ArticleText { get; set; }
    }
}

Unfortunately, I cannot make it: I am getting various EfCore5 translation errors and don't know: if it's me or EfCore5 bugs or limitations. I wasted 3 days trying different approaches. Please help - I was unable to find suitable examples in Internet. The problem comes up when I try to fill ArticleTexts property.

Here is the simplified example:

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

    var articlesQuery =
        (from articleIds in dbSet.Select(x => x.ArticleId).Distinct()
        from articlesPerId in dbSet
            .Where(x => x.ArticleId == articleIds.ArticleId)
        join askedL in dbContext.Langs
            .Where(l => l.LanguageCode == userContentLangRestriction)
            on
                articlesPerId.LanguageCode
                equals
                askedL.StringValue
            into askedLanguages
        from askedLawLanguage in askedLanguages.DefaultIfEmpty()
        join fallbackL in dbContext.Langs
            .Where(l => l.LanguageCode == CoreConstants.LanguageCodes.English)
        on
            articlesPerId.LanguageCode
            equals
            fallbackL.StringValue
        into fallbackLanguages
        from fallbackLanguage in fallbackLanguages.DefaultIfEmpty()
        select new
        {
            ArticleId = articleIds.ArticleId,
            ArticleText = articlesPerId.ArticleText,
            LanguageName = askedLawLanguage.ShortName ?? fallbackLanguage.ShortName
        })
        .OrderBy(x => x.ArticleId).ThenBy(x => x.LanguageName).ThenBy(x => x.ArticleText);

    await articlesQuery.LoadAsync();

    var aggregatedArticleData = articlesQuery.Select(x => new
    {
        ArticleId = x.ArticleId,
        ArticleText = x.ArticleText,
        LanguageName = x.LanguageName
    });

    var aggregatedArticles = articlesQuery.Select(x => x.ArticleId).Distinct().Select(x => new ArticleAggregated
    {
        ArticleId = x.ArticleId,
        ArticleTexts = aggregatedArticleData.Where(a => a.ArticleId == x.ArticleId)
            .Select(x => new LawArticleAggregated.Data
            {
                ArticleText = x.ArticleText,
                LanguageName = x.LanguageName
            }).ToList()
    });

    return aggregatedArticles;
}

For this specific code the exception is as follows:

Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.

标签： linqiqueryableef-core-5.0

Answer 1

我最终得到了以下实现（我“按原样”显示它，没有从第一条消息中简化来演示该方法，对初始变体进行了稍微修改以使用正确的分页）：

private async Task<IEnumerable<LawArticleAggregated>> GetLawArticlesGroupedByIdListAsync(
    DbSet<LawArticleDetail> dbSet,
    Expression<Func<IQueryable<LawArticleDetail>, IQueryable<LawArticleDetail>>> filterFunc,
    int skipCount,
    int maxResultCount,
    string userContentLangRestriction = null,
    CancellationToken cancellationToken = default
)
{
    var dbContext = await GetDbContextAsync();

    var articlesQuery =
        (from articleIds in filterFunc.Compile().Invoke(dbSet).Select(x => new { x.TenantId, x.LawArticleId })
            .Distinct().OrderBy(x => x.TenantId).OrderByDescending(x => x.LawArticleId).Skip(skipCount).Take(maxResultCount)
         from articlesPerId in dbSet
            .Where(x => x.TenantId == articleIds.TenantId && x.LawArticleId == articleIds.LawArticleId)
         join askedL in dbContext.FixCodeValues
            .Where(l =>
                 l.DomainId == CoreConstants.Domains.CENTRAL_TOOLS
                 && l.CodeName == CoreConstants.FieldTypes.LANGUAGE
                 && l.LanguageCode == userContentLangRestriction)
             on
                 articlesPerId.LanguageCode
                 equals
                 askedL.StringValue
             into askedLanguages
         from askedLawLanguage in askedLanguages.DefaultIfEmpty()
         join fallbackL in dbContext.FixCodeValues
            .Where(l =>
                 l.DomainId == CoreConstants.Domains.CENTRAL_TOOLS
                 && l.CodeName == CoreConstants.FieldTypes.LANGUAGE
                 && l.LanguageCode == CoreConstants.LanguageCodes.English)
         on
            articlesPerId.LanguageCode
            equals
            fallbackL.StringValue
         into fallbackLanguages
         from fallbackLanguage in fallbackLanguages.DefaultIfEmpty()
         select new
         {
             TenantId = articleIds.TenantId,
             LawArticleId = articleIds.LawArticleId,
             Shortcut = articlesPerId.Shortcut,
             ArticleText = articlesPerId.ArticleText,
             LanguageName = askedLawLanguage.ShortName ?? fallbackLanguage.ShortName
         })
        .OrderBy(x => x.TenantId).ThenByDescending(x => x.LawArticleId).ThenBy(x => x.Shortcut).ThenBy(x => x.LanguageName).ThenBy(x => x.ArticleText);

    var articleList = await articlesQuery.ToListAsync(cancellationToken);

    var aggregatedArticles = articleList.GroupBy(x => new { x.TenantId, x.LawArticleId })
        .Select(g => new LawArticleAggregated
        {
            TenantId = g.Key.TenantId,
            LawArticleId = g.Key.LawArticleId,
            ArticleTexts = g.Select(x => new LawArticleAggregated.Data
            {
                Shortcut = x.Shortcut,
                ArticleText = x.ArticleText,
                LanguageName = x.LanguageName
            }).ToList()
        });

    return aggregatedArticles;
}

private async Task<long> GetLawArticlesGroupedByIdCountAsync(
    DbSet<LawArticleDetail> dbSet,
    Expression<Func<IQueryable<LawArticleDetail>, IQueryable<LawArticleDetail>>> filterFunc,
    CancellationToken cancellationToken = default
)
{
    return await filterFunc.Compile().Invoke(dbSet).GroupBy(x => new { x.TenantId, x.LawArticleId }).LongCountAsync(cancellationToken);
}