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

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**

```c#
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:**

```c#
The LINQ expression 'DbSet()
.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()
.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**

```c#
.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,
'(.Lambda #Lambda1)),
0),
40),
'(.Lambda #Lambda2)),
'(.Lambda #Lambda3))
,
'(.Lambda #Lambda4f__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(Divsoft.ERP.Domain.Entities.Pessoa $item)
{
$item.Email
}

.Lambda #Lambda2(Divsoft.ERP.Domain.Entities.Pessoa $item)
{
$item.Email
}

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

.Lambda #Lambda4f__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)._db).Pessoa,
'(.Lambda #Lambda5f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String]]>))
,
'(.Lambda #Lambda6f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String],System.String]>))
,
'(.Lambda #Lambda7f__AnonymousType2`7[System.Guid,System.Int32,System.String,System.String,System.String,System.String,System.String],System.Boolean]>))
)),
$g)
}

.Lambda #Lambda5f__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 #Lambda6f__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 #Lambda7f__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