Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side

Question

I have a list of items "listItems", like this

Id Code Value
1  'a'  '1'
2  'a'  '2'
3  'b'  'x'
4  'b'  'y'

and to obtain the follwing

'a' => '1', '2'
'b' => 'x', 'y'

I use the following code by the EF Core

public async Task<Dictionary<string, List<string>>> GetAllListsAsync() =>
    await _context.ListItems
            .GroupBy(x => x.Code)
            .OrderBy(x => x.Key)
            .ToDictionaryAsync(x => x.Key, x => x.Select(y => y.Value)
                                                 .OrderBy(v => v)
                                                 .ToList());

I obtain in the Swagger's API the following error:

System.InvalidOperationException: Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side. at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, LambdaExpression& relatedDataLoaders) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQuery(ShapedQueryExpression shapedQueryExpression)

---

What I tried to do:

• If I use AsEnumerable before the GroupBy, it does not find ToDictionaryAsync anymore:

• I tested to add AsQueryableafter the AsEnumlerable the compile error message disapeared in compile time, but in runtime remained the same (Unable to translate 'GroupBy' pattern. Call 'AsEnumerable' before ...)

• I tried also this one

    public async Task<Dictionary<string, List<string>>> GetAllListsAsync() =>
        await _context.ListItems
            .ToLookup(x => x.Code, x => x.Value)
            .AsQueryable()
            .ToDictionaryAsync(x => x.Key, x => x.Select(y => y)
                                                 .ToList());
  

the runtime says in that case:

The source 'IQueryable' doesn't implement 'IAsyncEnumerable<System.Linq.IGrouping2[System.String,System.String]>'. Only sources that implement 'IAsyncEnumerable' can be used for Entity Framework asynchronous operations. at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsAsyncEnumerable[TSource](IQueryable1 source) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToDictionaryAsync[TSource,TKey,TElement](IQueryable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer, CancellationToken cancellationToken)