GroupBy with SelectMany and Take() - The LINQ expression could not be translated

[buecherlstefan]

Include your code

public IQueryable<ProductInfoRawData> ListLastInsertedOnesPerApi(int count = 5)
    {
        var query = _repository.Query
                .GroupBy(x => x.ApiName)
                .SelectMany(x => x.Take(count))
            ;

        return query;
    }

Include stack traces

The LINQ expression 'DbSet<ProductInfoRawData>()
    .GroupBy(x => x.ApiName)
    .SelectMany(x => x
        .AsQueryable()
        .Take(__count_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.

   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Zollner.ProductService.Logic.Business.ProductInfoWorkflows.ProductInfoServiceApiInformationCollector.<CollectApiInformationAsync>d__6.MoveNext() in C:\Projekte\product-service\ProductInfoWorkflows\ProductInfoServiceApiInformationCollector.cs:line 33

Include provider and version information

EF Core version: 6.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.1.6

[roji]

In SQL, GROUP BY requires using an aggregate operator - Count, Max - to reduce the grouped rows. See the docs for more information.

[smitpatel]

You can write like this

var query = _repository.Query
                .GroupBy(x => x.ApiName)
                .Select(g => g.Take(count))
                .SelectMany(x => x)
            ;

[coridrew]

You can write like this

var query = _repository.Query
                .GroupBy(x => x.ApiName)
                .Select(g => g.Take(count))
                .SelectMany(x => x)
            ;

This fails with this btw:
"error": "System.InvalidOperationException: The LINQ expression 'x => x' 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'.

[gquinn1998]

I am having exactly the same issues, I am finding the Groupby does not work at all

[Tantol]

I stumbled up on the same issue. GroupBy is not working with SelectMany.

[Luk164]

I think I might be having the same issue, did anyone find a workaround yet?

[coridrew]

I think I might be having the same issue, did anyone find a workaround yet?

I did not.

[buecherlstefan]

Workaround for me is to retrieve the data and then SelectMany(), so the expression don't have to be translated to sql

public IEnumerable<ProductInfoRawData> ListLastInsertedOnesPerApi(int count = 5)
    {
        //
        // LISTING 1
        // var query = _repository.Query.GroupBy(x => x.ApiName).SelectMany(x => x.Take(count));
        //

        // LISTING 1 throws The LINQ expression could not be translated
        // FIX: Do it with IEnumerable

        var enumerable = _repository.Query
            .GroupBy(x => x.ApiName)
            .Select(x => x.OrderByDescending(y => y.DateUtc).Take(count))
            .ToList()
            .SelectMany(x => x);

        return enumerable;
    }

[donguyenthanhlan]

This is my solution. I replace the code just for demo purposes.

_context.Tags
                    .Where(x => x.CreatedBy == request.CreatedBy)
                    .GroupBy(x => x.MeasuredAt)
                    .OrderByDescending(y => y.Key)
                    .Take(5)
                    .Select(x => x.Select(x => new NewModel {
                        Id = x.Id,
                        CreatedAt = x.CreatedAt,
                        CreatedBy = x.CreatedBy,
                        MeasuredAt = x.MeasuredAt,
                        Value = x.Value
                    }))
                    .ToList()
                    .SelectMany(x => x);

[Poltuu]

I'm surprised that @donguyenthanhlan solution works, and specially when the following does not

_context.Tags
                    .Where(x => x.CreatedBy == request.CreatedBy)
                    .GroupBy(x => x.MeasuredAt)
                    .OrderByDescending(y => y.Key)
                    .Take(5)
                    .SelectMany(x => x.Select(x => new NewModel {
                        Id = x.Id,
                        CreatedAt = x.CreatedAt,
                        CreatedBy = x.CreatedBy,
                        MeasuredAt = x.MeasuredAt,
                        Value = x.Value
                    }))
                    .ToList();

Is there any plan/way to support such requests ? They seem pretty regular to me.

[roji]

@Poltuu yeah, we'll likely do a big push on GroupBy functionality - there's a broad set of issues in the implementation that need to be fixed. That's unfortunately unlikely to happen for 9.0 - but I'm hoping we'll do it for 10 at the latest.