Query: GroupBy(SubQuery) throws exception

[smitpatel]

In Northwind,

[ConditionalFact]
        public virtual void Test()
        {
            using (var context = CreateContext())
            {
                var query = context.Orders.GroupBy(o => context.Customers.Where(c => c.CustomerID == o.CustomerID).Select(c => c.ContactName)).ToList();
            }
        }

Exception

------ Test started: Assembly: Microsoft.EntityFrameworkCore.InMemory.FunctionalTests.dll ------

Test 'Microsoft.EntityFrameworkCore.Query.GroupByQueryInMemoryTest.Test' failed: System.ArgumentException : Expression of type 'System.Collections.Generic.IEnumerable`1[System.Linq.IGrouping`2[System.Collections.Generic.IEnumerable`1[System.String],Microsoft.EntityFrameworkCore.TestModels.Northwind.Order]]' cannot be used for return type 'System.Collections.Generic.IEnumerable`1[System.Linq.IGrouping`2[System.Linq.IQueryable`1[System.String],Microsoft.EntityFrameworkCore.TestModels.Northwind.Order]]'
	at System.Linq.Expressions.Expression.ValidateLambdaArgs(Type delegateType, Expression& body, ReadOnlyCollection`1 parameters)
	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.EntityQueryModelVisitor.CreateExecutorLambda[TResults]()
	at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
	at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
	--- End of stack trace from previous location where exception was thrown ---
	at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
	at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
	at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass13_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 Remotion.Linq.QueryableBase`1.GetEnumerator()
	at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
	at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
	at Microsoft.EntityFrameworkCore.Query.GroupByQueryTestBase`1.Test()
	D:\code\SecondEF\EntityFrameworkCore\test\EFCore.InMemory.FunctionalTests\Query\GroupByQueryInMemoryTest.cs(29,0): at Microsoft.EntityFrameworkCore.Query.GroupByQueryInMemoryTest.Test()

0 passed, 1 failed, 0 skipped, took 2.18 seconds (xUnit.net 2.3.1 build 3858).

Fails for SqlServer/InMemory.
Works in linq.

QM

 Compiling query model: 
  '(from Order o in DbSet<Order>
  select [o]).GroupBy(
          from Customer c in DbSet<Customer>
          where [c].CustomerID == [o].CustomerID
          select [c].ContactName, [o])'
  
  Optimized query model: 
  '(from Order o in DbSet<Order>
  select [o]).GroupBy(
          from Customer c in DbSet<Customer>
          where [c].CustomerID == [o].CustomerID
          select [c].ContactName, [o])'
  
  The LINQ expression 'GroupBy({from Customer c in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Microsoft.EntityFrameworkCore.TestModels.Northwind.Customer]) where ([c].CustomerID == [o].CustomerID) select [c].ContactName}, [o])' could not be translated and will be evaluated locally.

[smitpatel]

Above query is grouping by IQueryable which is not a scalar hence cannot be translated to server. Applying FirstOrDefault to make it scalar works for InMemory/Sqlite. Fails in SqlServer. Filing new issue.

[smitpatel]

Non-scalars are not allowed in SQL GROUP BY.