Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported

[adrianotrentim]

The below LINQ query works perfectly fine in EF6 world where the whole query seems to be evaluated at the server but fails in EF Core 9.

- Query

var temp = _db.Pessoa
    .OrderBy(item => item.Email)
    .Skip(0)
    .Take(40)
    .GroupBy(item => item.Email)
    .OrderBy(g => g.Key)
    .Select(g => new
    {
        Key = g.Key,
        ItemCount = g.Count(),
        HasSubgroups = false,
        Member = "Email",
        AggregateFunctionsProjection = new
        {
            Count_Referencia = _db.Pessoa
                    .Select(t => new
                    {
                        t.IdPessoa,
                        t.Referencia,
                        t.Nome_RazaoSocial,
                        t.Apelido_Fantasia,
                        t.CPF_CNPJ,
                        t.RG_IE,
                        t.Email
                    })
                    .OrderBy(item => item.Email)
                    .Where(item => item.Email == g.Key)
                    .Count()
        },
        Items = g
    }
    )
    .ToList();

- Exception:

The LINQ expression 'DbSet<Pessoa>()
    .OrderBy(item => item.Email)
    .Skip(__p_0)
    .Take(__p_1)
    .GroupBy(item => item.Email)
    .OrderBy(g => g.Key)
    .Select(g => new { 
        Key = g.Key, 
        ItemCount = g
            .AsQueryable()
            .Count(), 
        HasSubgroups = False, 
        Member = "Email", 
        AggregateFunctionsProjection = new { Count_Referencia = DbSet<Pessoa>()
            .Select(t => new { 
                IdPessoa = t.IdPessoa, 
                Referencia = t.Referencia, 
                Nome_RazaoSocial = t.Nome_RazaoSocial, 
                Apelido_Fantasia = t.Apelido_Fantasia, 
                CPF_CNPJ = t.CPF_CNPJ, 
                RG_IE = t.RG_IE, 
                Email = t.Email
             })
            .OrderBy(item => item.Email)
            .Where(item => item.Email == g.Key)
            .Count() }, 
        Items = g
     })' could not be translated. Additional information: Translation of 'Select' which contains grouping parameter without composition is not supported. 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.'

- Expression Tree

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.OrderBy(
        .Call System.Linq.Queryable.GroupBy(
            .Call System.Linq.Queryable.Take(
                .Call System.Linq.Queryable.Skip(
                    .Call System.Linq.Queryable.OrderBy(
                        .Extension<Microsoft.EntityFrameworkCore.Query.EntityQueryRootExpression>,
                        '(.Lambda #Lambda1<System.Func`2[Divsoft.ERP.Domain.Entities.Pessoa,System.String]>)),
                    0),
                40),
            '(.Lambda #Lambda2<System.Func`2[Divsoft.ERP.Domain.Entities.Pessoa,System.String]>)),
        '(.Lambda #Lambda3<System.Func`2[System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa],System.String]>))
    ,
    '(.Lambda #Lambda4<System.Func`2[System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa],<>f__AnonymousType0`6[System.String,System.Int32,System.Boolean,System.String,<>f__AnonymousType1`1[System.Int32],System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa]]]>))

.Lambda #Lambda1<System.Func`2[Divsoft.ERP.Domain.Entities.Pessoa,System.String]>(Divsoft.ERP.Domain.Entities.Pessoa $item)
{
    $item.Email
}

.Lambda #Lambda2<System.Func`2[Divsoft.ERP.Domain.Entities.Pessoa,System.String]>(Divsoft.ERP.Domain.Entities.Pessoa $item)
{
    $item.Email
}

.Lambda #Lambda3<System.Func`2[System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa],System.String]>(System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa] $g)
{
    $g.Key
}

.Lambda #Lambda4<System.Func`2[System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa],<>f__AnonymousType0`6[System.String,System.Int32,System.Boolean,System.String,<>f__AnonymousType1`1[System.Int32],System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa]]]>(System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa] $g)
{
    .New <>f__AnonymousType0`6[System.String,System.Int32,System.Boolean,System.String,<>f__AnonymousType1`1[System.Int32],System.Linq.IGrouping`2[System.String,Divsoft.ERP.Domain.Entities.Pessoa]](
        $g.Key,
        .Call System.Linq.Enumerable.Count($g),
        False,
        "Email",
        .New <>f__AnonymousType1`1[System.Int32](.Call System.Linq.Queryable.Count(.Call System.Linq.Queryable.Where(
                    .Call System.Linq.Queryable.OrderBy(
                        .Call System.Linq.Queryable.Select(
                            (.Constant<Divsoft.ERP.Web.Controllers.HomeController>(Divsoft.ERP.Web.Controllers.HomeController)._db).Pessoa,
                            '(.Lambda #Lambda5<System.Func`2[Divsoft.ERP.Domain.Entities.Pessoa,<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String]]>))
                        ,
                        '(.Lambda #Lambda6<System.Func`2[<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String],System.String]>))
                    ,
                    '(.Lambda #Lambda7<System.Func`2[<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String],System.Boolean]>))
            )),
        $g)
}

.Lambda #Lambda5<System.Func`2[Divsoft.ERP.Domain.Entities.Pessoa,<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String]]>(Divsoft.ERP.Domain.Entities.Pessoa $t)
{
    .New <>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String](
        $t.IdPessoa,
        $t.Referencia,
        $t.Nome_RazaoSocial,
        $t.Apelido_Fantasia,
        $t.CPF_CNPJ,
        $t.RG_IE,
        $t.Email)
}

.Lambda #Lambda6<System.Func`2[<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String],System.String]>(<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String] $item)
{
    $item.Email
}

.Lambda #Lambda7<System.Func`2[<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String],System.Boolean]>(<>f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String] $item)
{
    $item.Email == $g.Key
}

- Informations

EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 9.0
Operating system: Win 11 Pro
IDE: Visual Studio 2022 17.12