Group by with projection (without aggregate)

[stevozilik]

Group by with projection

Group by (that works in Entity Framework), does not work in EF Core:

Simplest repro code

public class Driver
{
    public System.Guid Id { get; set; }
    public int Type { get; set; }
    public bool IsAlias { get; set; }
}

(from d in db.Drivers
 group d by new { d.Type, d.IsAlias } into g
 select new { Key = g.Key,  Records = g  })
 .ToList();

Ultimately I understand that this is mostly a client side projection, not actually group by in database. The reason why it's interesting is that it enables combining db queries and client-side projections (that seems supported by EF) in one linq statement, with complex inner filters & joins as below:

Real world use case example

from cuc in db.ChampionshipUserClasses
where cuc.DomainId == domainId && cuc.ChampionshipClass.ChampionshipId == model.Id
group cuc by new { cuc.ChampionshipClass.Name, cuc.ChampionshipClassId } into cucByName
select new
{
    ClassId = cucByName.Key.ChampionshipClassId
    Drivers = (from uc in cucByName
               where ((!onlyPositioned || uc.Position.HasValue) && (uc.Points.HasValue || uc.IsActive))
               orderby uc.Position
               let driver = uc.ChampionshipUser.DomainUser.Drivers.Where(d => !d.IsAlias).FirstOrDefault()
               let team = uc.ChampionshipUser.DomainUser.Team
               select new MemberStanding()
               {
                   DriverId = driver.Id,
                   ChampionshipUserId = uc.ChampionshipUserId,
                   DomainUserId = uc.ChampionshipUser.DomainUserId,
                   Name = driver.Name,
                   UniqueName = driver.UniqueName,
                   TeamName = team == null ? null : team.Name,
                   TeamPrincipalId = team == null ? null : (Guid?)team.PrincipalId,
                   TeamId = uc.ChampionshipUser.DomainUser.TeamId,
                   Position = uc.Position,
                   Points = uc.Points,
                   DateCreated = uc.DateCreated,
                   IsClassActive = uc.IsActive,
                   IsActive = uc.ChampionshipUser.IsActive,
                   IsSuspended = uc.ChampionshipUser.IsSuspended,
                   SuspendedReason = uc.ChampionshipUser.SuspendedReason,
                   UserId = uc.ChampionshipUser.DomainUser.UserId
               }).Take(topCountValue)
};

Error

(from the repro example)

'Processing of the LINQ expression 'GroupByShaperExpression:
KeySelector: new {
Type = d.Type,
IsAlias = d.IsAlias
},
ElementSelector:EntityShaperExpression:
EntityType: Driver
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
' by 'RelationalProjectionBindingExpressionVisitor' failed

Include provider and version information

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

Bug / Feature Request?

Is this meant to work in EF Core (i.e. a bug)?
Or do I just need to tweak EF Core settings for this to work?
Or are similar linq statements not planned to be supported in EF Core?

As a side note, I could not find any guide on differences between EF and EF Core - example queries that used to work and now they don't. Any tips?

The official guide seems to ignore the (for me shocking) lack of support for LINQ statements that used to work in EF and are not working/supported in EF Core. Reading the guide one gets the impression that these frameworks are very close to feature parity.

Regards,
Stefan

[ajcvickers]

/cc @smitpatel

[smitpatel]

Duplicate of #19929

[ajcvickers]

This is closed as a duplicate of #19929, but still fails with EF7 RC2:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Driver>()
    .GroupBy(d => new {
        Type = d.Type,
        IsAlias = d.IsAlias
     })
    .Select(g => new {
        Key = g.Key,
        Records = 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.
   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 Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 92

[smitpatel]

Only final GroupBy operation is supported. Using grouping parameter g without composition in any other form is not supported.