Query: GroupBy in-memory execution must honor result retrieved by the query executed against database

[dozer75]

When a query that uses a db function is grouped using GroupBy in linq the C# function is executed when the GroupBy is executed in-memory even if it is executed in the database query already. It should not call the db function in these scenarios and rather use the value returned by the query.

This is the LINQ statement that causes the exception. Full code is attached in zip file.

context.SimpleTables.Select(row => new
                                   {
                                       row.Id,
                                       row.Val1,
                                       row.Val2,
                                       Sum = Functions.MySum(row.Val1, row.Val2)
                                   })
                     .GroupBy(row => row.Id, row => new
                                                    {
                                                        row.Val1,
                                                        row.Val2,
                                                        row.Sum
                                                    })
                     .ToArray();

Steps to reproduce

The solution is in whole added to this issue. There is a database script that creates the needed data to reproduce it on SQL Server. The project is an xUnit test that will fail with the NotSupportedException that is specified in the db function definition.

EFCore21GroupByDbFunctions.zip

Further technical details

EF Core version: 2.1.0-preview1-27020 (The issue is also in 2.0)
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017.3

[ajcvickers]

Triage: @smitpatel to investigate whether this should be supported (and if so whether it can be done without relinq changes), or whether writing the GroupBy in a different way is a more practical approach.

[smitpatel]

This is because of how the ReLinq generates QueryModel

      Compiling query model:
      '(from Blog row in DbSet<Blog>
      select new <>f__AnonymousType1<int, int, int, int>(
          [row].Id,
          [row].Val1,
          [row].Val2,
          int MySum(
              a: [row].Val1,
              b: [row].Val2)
      )).GroupBy([row].Id, new <>f__AnonymousType2`3(Val1 = [row].Val1, Val2 = [row].Val2, Sum = MySum([row].Val1, [row].Val2)))'

This causes MySum to evaluate on client during element selector.

[dozer75]

Have a hard time seeing why it shouldn't be supported, the data is retrieved from the databasse before grouped in memory, so why not use the inline result. Limited group by support with in-memory calculations was acceptable for us, but this is a showstopper for further usage of Entity Framework Core :/

[smitpatel]

Duplicate of #17068