Join with group by throwing exception

[SoftCircuits]

Ask a question

Does anyone know why this query fails? Or how to work around it?

var user = await (from u in DbContext.Users
                  join ur in DbContext.UserRoles on u.Id equals ur.UserId
                  group ur.RoleId by u into g
                  select new
                  {
                      g.Key.Id,
                      g.Key.Email,
                      g.Key.EmailConfirmed,
                      UserRoles = g
                  })
                  .FirstOrDefaultAsync();

The exception message really isn't helpful.

System.InvalidOperationException: 'The LINQ expression 'DbSet<ApplicationUser>()
    .Join(
        inner: DbSet<IdentityUserRole<string>>(), 
        outerKeySelector: a => a.Id, 
        innerKeySelector: i => i.UserId, 
        resultSelector: (a, i) => new TransparentIdentifier<ApplicationUser, IdentityUserRole<string>>(
            Outer = a, 
            Inner = i
        ))
    .GroupBy(
        keySelector: ti => ti.Outer, 
        elementSelector: ti => ti.Inner.RoleId)' 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've tried so many variations on this but there seems to be one problem or another.

Include provider and version information

EF Core version: 5.0.9
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.11.2)

[smitpatel]

Duplicate of #17653

GroupBy entity type is not supported yet.