The LINQ expression cannot be translated on Grouping-CodePudding

I have LINQ expression to take top 15 most frequently used answers

Here is expression

 var latestAnswers = await _dbContext.TextAnswers.Include(x => x.CompanySurvey).ThenInclude(x => x.Survey)
        .Where(x => x.CompanySurvey.Survey.FiscalYear == 2022)
        .GroupBy(x => x.Answer)
        .OrderByDescending(g => g.Count())
        .Take(15)
        .ToListAsync();

But I get this error

The LINQ expression 'DbSet() .Include(x => x.CompanySurvey) .ThenInclude(x => x.Survey) .Where(x => x.CompanySurvey.Survey.FiscalYear == (int?)2022) .GroupBy(x => x.Answer) .OrderByDescending(g => g .AsQueryable() .Count()) .Take(__p_0)' 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.

I can't understood why it cant be translated.

How I can fix this?

CodePudding user response：

EF Core 6 has to support such queries, but looks like full implementation support is moved to EF Core 7

After conversation looks like it is not needed to get grouped records from database, but just grouping key and Count

var latestAnswers = await _dbContext.TextAnswers
    .Where(x => x.CompanySurvey.Survey.FiscalYear == 2022)
    .GroupBy(x => x.Answer)
    .Select(g => new { Answer = g.Key, Count = g.Count() })
    .OrderByDescending(x => x.Count)
    .Take(15)
    .ToListAsync();

CodePudding user response：

I rewrite my code like this

 var latestAnswersQuery = await _dbContext.TextAnswers
        .Include(x => x.CompanySurvey).ThenInclude(x => x.Survey)
        .Where(x => x.CompanySurvey.Survey.FiscalYear == 2022)
        .ToListAsync();

    var latestAnswers = latestAnswersQuery.GroupBy(x => x.Answer).OrderByDescending(g => g.Count()).Take(15);
    return latestAnswers;

And now everything great