Support for GroupBy entityType

[ares128]

Update by @smitpatel

Certain databases support projecting columns after SQL GROUP BY which are neither contained in aggregate nor in grouping key. (SqlServer disallows this). This means that GroupBy entity type when aggregate is applied can be translated to server by grouping by PK and then projecting all the columns for materialization.

---

Original post:

An InvalidOperationException is throwed when GroupBy on then Include property. But same code works on ef core 2.X.

System.InvalidOperationException: The LINQ expression 'EntityShaperExpression:
    EntityType: Author
    ValueBufferExpression:
        ProjectionBindingExpression: Inner
    IsNullable: False
' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupingKey(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   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.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at EFGroupByBug.Program.Main(String[] args) in C:\Users\ares1\source\repos\EFGroupByBug\EFGroupByBug\Program.cs:line 44

   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 StoreContext : DbContext
    {
        public DbSet<Author> Authors { get; set; } = default!;

        public DbSet<Book> Books { get; set; } = default!;

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite(@"Data Source=test.db;");
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (var context = new StoreContext())
                {
                    var array = context.Books.Include(s => s.Author)
                        .GroupBy(s => s.Author)
                        .Select(s => new { author = s.Key, maxPrice = s.Max(p => p.Price) })
                        .ToArray();

                    Console.WriteLine(array.Length);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

        }
    }

Current version:
EF Core version: 3.0.0-preview9.19423.6

[smitpatel]

GroupBy entityType cannot be server translated to SQL GROUP BY directly.

[ares128]

But same code works on ef core 2.x

[smitpatel]

It evaluated that on client side.

[smitpatel]

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

[ares128]

Ok I Got it, but i think it can be translated to

SELECT max("b"."Price"), "a"."Id", "a"."Name"
      FROM "Books" AS "b"
      INNER JOIN "Authors" AS "a" ON "b"."AuthorId" = "a"."Id" 
	  GROUP BY "a"."Id";

Can you consider implement this?
Thx

[smitpatel]

It is invalid SQL
Column Authors.Name is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.`

[ares128]

actually it is, look at the pic

[smitpatel]

Seems like it works on Sqlite. Fails on SqlServer. We will investigate more in which database supports and make a decision. Thanks for pointing to that.

[ares128]

thank you

[ares128]

For sqlserver, you need add all property in Group by clause.

sqlite support this too.
so does mysql

[GSPP]

Pulling over some thoughts from another issue:

Group by supports grouping on scalars and anonymous types. It should also support grouping on an entity type. This should behave the same way as grouping on an anonymous type with the same columns that the entity type has.

This can be translated to SQL by grouping on all columns of that entity type.

Alternatively, grouping can be performed on the primary key columns and all other columns can be re-created through MAX(nonPKColumn).

These translation forms should be possible for all RDBMSes.

This is a LINQ to SQL migration hurdle.

[smitpatel]

Looking at some ef6 generated queries, it seems like we put all columns inside Group by clause. That could be one implementation (may not be most ideal)