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

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.

CodePudding user response：

I think I have reverse engineered your query. Big difference that we cannot return IQueryable from this funcrtio, but prepared IEnumerable. So if you have pagination later, better to pass page info into function parameters.

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

    var articlesQuery =
        from article in dbSet
        from askedLawLanguage in dbContext.Langs
            .Where(askedLawLanguage => askedLawLanguage.LanguageCode == userContentLangRestriction && article.LanguageCode == askedLawLanguage.StringValue)
            .DefaultIfEmpty()
        from fallbackLanguage in dbContext.Langs
            .Where(fallbackLanguage => fallbackLanguage.LanguageCode == CoreConstants.LanguageCodes.English && article.LanguageCode == fallbackLanguage.StringValue)
            .DefaultIfEmpty()
        select new
        {
            ArticleId = article.ArticleId,
            ArticleText = article.ArticleText,
            LanguageName = askedLawLanguage.ShortName ?? fallbackLanguage.ShortName
        };

    articlesQuery = articlesQuery
        .OrderBy(x => x.ArticleId)
        .ThenBy(x => x.LanguageName)
        .ThenBy(x => x.ArticleText);

    var loaded = await articlesQuery.ToListAsync();

    // group on the client side
    var aggregatedArticles = loaded.GroupBy(x => x.ArticleId)
        .Select(g => new ArticleAggregated
        {
            ArticleId = g.Key,
            ArticleTexts = g.Select(x => new LawArticleAggregated.Data
            {
                ArticleText = x.ArticleText,
                LanguageName = x.LanguageName
            }).ToList()
        });

    return aggregatedArticles;
}

CodePudding user response：

I ended up with the following implementation (I show it "as is", without simplification from the first message to demonstrate the approach, slightly modified from the initial variant to use proper paging):

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);
}