GroupBy LINQ expression could not be translated and is evaluated locally

[xmichaelx]

LINQ GroupBy expression generated by OData odata/file?$apply=groupby((FileType)) is not translated to SQL group by and executed locally which leads to exception being thrown

OData.Test>       The LINQ expression 'GroupBy(new GroupByWrapper() {GroupByContainer = new LastInChain() {Name = "FileType", Value = [$it].FileType}}, [$it])' could not be translated and will be evaluated locally.
OData.Test> warn: Microsoft.EntityFrameworkCore.Query[20500]
OData.Test>       The LINQ expression 'GroupBy(new GroupByWrapper() {GroupByContainer = new LastInChain() {Name = "FileType", Value = [$it].FileType}}, [$it])' could not be translated and will be evaluated locally.
OData.Test> info: Microsoft.EntityFrameworkCore.Database.Command[20101]
OData.Test>       Executed DbCommand (44ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
OData.Test>       SELECT [$it0].[Id], [$it0].[FileType]
OData.Test>       FROM [Files] AS [$it0]
OData.Test> fail: Microsoft.EntityFrameworkCore.Query[10100]
OData.Test>       An exception occurred in the database while iterating the results of a query for context type 'OData.Models.FileContext'.
OData.Test>       System.Security.VerificationException: operation could destabilize the runtime.
OData.Test>          w lambda_method(Closure , File )
OData.Test>          w System.Linq.Lookup`2.Create[TSource](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
OData.Test>          w System.Linq.GroupedEnumerable`3.GetEnumerator()
OData.Test>          w System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
OData.Test>          w Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

Steps to reproduce

Run test solution: https://github.com/xmichaelx/ODataGroupByTest

In issue-workaround branch there is a workaround using linq2db which generated group by correctly.

Further technical details

EF Core version: 2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 Pro
IDE: Visual Studio 2017 15.7.5

[xmichaelx]

Same issue as in: OData/WebApi#1578

[ajcvickers]

@smitpatel to take a look

[smitpatel]

The exception message here is
System.Security.VerificationException: operation could destabilize the runtime.
We don't throw such exception. The codebase which generates the exception should be first one to investigate into it. It may be some bug in EF Core too but in present state, there is no way for us to investigate anything. Also check out OData/WebApi#1413

[xmichaelx]

I've investigated further and accessed the expression tree associated with query that is generated using following code:

public IActionResult Get(ODataQueryOptions<File> oDataQueryOptions)
{
    var expr = oDataQueryOptions.ApplyTo(_db.Files).Expression;
    return Ok(_db.Files);
}

DebugView from expression:

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.GroupBy(
        .Constant<Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[OData.Models.File]>(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[OData.Models.File]),
        '(.Lambda #Lambda1<System.Func`2[OData.Models.File,Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper]>)),
    '(.Lambda #Lambda2<System.Func`2[System.Linq.IGrouping`2[Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper,OData.Models.File],Microsoft.AspNet.OData.Query.Expressions.AggregationWrapper]>))

.Lambda #Lambda1<System.Func`2[OData.Models.File,Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper]>(OData.Models.File $$it)
{
    .New Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper(){
        GroupByContainer = .New Microsoft.AspNet.OData.Query.Expressions.AggregationPropertyContainer+LastInChain(){
            Name = "FileType",
            Value = $$it.FileType
        }
    }
}

.Lambda #Lambda2<System.Func`2[System.Linq.IGrouping`2[Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper,OData.Models.File],Microsoft.AspNet.OData.Query.Expressions.AggregationWrapper]>(System.Linq.IGrouping`2[Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper,OData.Models.File] $$it)
{
    .New Microsoft.AspNet.OData.Query.Expressions.AggregationWrapper(){
        GroupByContainer = ($$it.Key).GroupByContainer
    }
}

HTH but since I see a lot of code from OData namespaces I guess I'll have to look for help there.

[ajcvickers]

Triage: closing as external since this is a case where OData is producing an invalid expression tree, as tracked by OData/WebApi#1413