GroupBy or Distinct, not supported in EF Core 3.1 in ToQuery operations for keyless types

[axthosarouris]

Steps to reproduce

I have a Database View that joins three tables and returns a some fields of the resulting tuples.
Essentially the view is the following:

CREATE OR ALTER VIEW [dbo].[View_Field] with schemabinding
AS SELECT DISTINCT
    Field.[ID],
   Field.[FieldName],
FROM
dbo.Field
JOIN dbo.Revision ON Revision.FieldID=Field.ID
JOIN dbo.Forecast ON Forecast.ID= Revision.ForecastID
GO

During testing I need to implement the view in the InMemory database. Until ef core 2.2. this was possible. Now distinct and grouping are not supported by EF core for the InMemory database.

The code for the InMemory database is the following:

  modelBuilder.Entity<View_Field>()
                            .HasNoKey()
                            .ToQuery(() =>
                                         Set<Field>()
                                             .Join(Set<Revision>(),
                                                   field => field.Id,
                                                   revision => revision.FieldId,
                                                   (field, revision) => new {field, revision})
                                             .Join(Set<Forecast>(),
                                                   firstJoin => firstJoin.revision.ForecastId,
                                                   forecast => forecast.Id,
                                                   (firstJoin, forecast) => firstJoin.field)
                                              .GroupBy(f=>f.Id)
                                             .Select(g=>g.FirstOrDefault())
                                             .Select(f => new View_Field
                                                          {
                                                              Id = f.Id, FieldName = f.FieldName
                                                          })
                                            
                                    );

The thrownException is the following:

CenturiesWebApi.Test.Services.ShortTermForecastStatusServiceTest.GetFieldStatusEntriesWithOeShouldReturnDistinctValues

System.InvalidOperationException : The LINQ expression '(GroupByShaperExpression:
KeySelector: EntityMaterializerSource.TryReadValue<int>(grouping.Key, 0, Property: Field.Id (int) Required PK AfterSave:Throw ValueGenerated.OnAdd), 
ElementSelector:(EntityShaperExpression: 
    EntityType: Field
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .First()' 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.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryExpressionTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryProjectionBindingExpressionVisitor.Translate(InMemoryQueryExpression queryExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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 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.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

The code runs if I remove the GroupBy command, but it produces duplicates. Distinct produces the same error.

The main problem that we have is that we need to have this table for testing the functionality of some functions. It used to work with .NET core 2.2. but now it does not

The code for the query has been implemented following @maumar's directions for the issue #19845

Further technical details

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.InMemory)
Target framework: .NET Core 3.1
Operating system: Windows

[smitpatel]

blocked on #20023