Query: Support GroupBy when it is final operator

[smitpatel]

This issue is about enabling following query

context.Orders.GroupBy(o => o.CustomerID).ToList();

This is NOT about client evaluating when there is any additional operator after GroupBy or result selector is specified in GroupBy.
Examples of disallowed queries where we will NOT client eval GroupBy operator

context.Orders.GroupBy(o => o.CustomerID).Select(g => new { g.Key, OrderDates = g.Select(e => e.OrderDate).ToList()).ToList();
context.Orders.GroupBy(o => o.CustomerID, (k, os) => new { OrderDates = os.Select(e => e.OrderDate).ToList()).ToList();

As of EF Core 3.1 certain constructs of GroupBy where GroupBy is composed over (followed by another queryable operator) is translated. Read our GroupBy operator documentation to understand what is supported scenarios.

There are few other cases, where GroupBy with composition can be translated to server but not yet translated. To list a few

• GroupBy-aggregate operator with another join (Query: Join after GroupByAggregate throws when join key uses grouping key or aggregate function #10012)
• Grouping-Where-Aggregate or Grouping-Select-Aggregate (Query: GroupBy Where Aggregate translation to server #11711/ Can't translate GroupBy with Conditional Aggregate #18836)
• Grouping-FirstOrDefault over the group (Translate GroupBy followed by FirstOrDefault over group #12088)
• Ability to select top N of each group (Support ability to select top N of each group #13805)
• Grouping-Distinct-Aggregate (Aggregate function distinct count throw InvalidOperationException #17376)
• GroupBy entity type (Support for GroupBy entityType #17653)

Resolution of this issue only enables client evaluation of GroupBy when it is last operator. Any of the above listed missing features will not work even if this issue is fixed. Please upvote relevant issue from above list which you need in your scenario. Please upvote this issue iff you need client side evaluation of GroupBy operator when it is last operator.

[ajcvickers]

Works for me when grouping by a string, but is failing for me when grouping by int:

var query = context.Books.GroupBy(s => s.Price);

Unhandled exception. System.ArgumentException: Expression of type 'Microsoft.EntityFrameworkCore.Query.Internal.GroupBySingleQueryingEnumerable`2[System.Nullable`1[System.Int32],Book]' cannot be used for return type 'System.Collections.Generic.IEnumerable`1[System.Linq.IGrouping`2[System.Int32,Book]]'
   at System.Linq.Expressions.Expression.ValidateLambdaArgs(Type delegateType, Expression& body, ReadOnlyCollection`1 parameters, String paramName)
   at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, String name, Boolean tailCall, IEnumerable`1 parameters)
   at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, Boolean tailCall, IEnumerable`1 parameters)
   at System.Linq.Expressions.Expression.Lambda[TDelegate](Expression body, ParameterExpression[] parameters)
   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 Program.Main() in C:\local\code\AllTogetherNow\Daily\Daily.cs:line 86

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
}

public class Book
{
    public int Id { get; set; }
    public Author Author { get; set; } = default!;
    public int Price { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString, b => b.UseNetTopologySuite())
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var toast = new Author { Name = "Toast" };
            var alice = new Author { Name = "Alice" };

            context.AddRange(
                new Book { Author = alice, Price = 10 },
                new Book { Author = alice, Price = 10 },
                new Book { Author = toast, Price = 12 },
                new Book { Author = toast, Price = 12 },
                new Book { Author = toast, Price = 14 });

            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var query = context.Books.GroupBy(s => s.Price);

            foreach (var group in query)
            {
                Console.WriteLine($"Price: {group.Key}; Count = {group.Count()}");
            }
        }
    }
}