groupby broken in .net core/efcore 3+ w odata 7.3

[MolallaComm]

It would appear that the issues discussed in:

https://docs.microsoft.com/en-us/ef/core/querying/client-eval

have broken $apply/groupby and friends when using .net core/efcore 3+ but hopefully someone here can/has come up with a workaround? Was testing $apply/groupby not part of the 7.3 release process for .net core or is 7.3 really not ready for prime time yet on .net core even though it is released - if not it might be good to put up a list of what works, and doesn't work on .net core somewhere so people know what to expect.

If I do http://localhost:5000/odata/OntDTO?$apply=groupby((ProvModel),aggregate($count%20as%20GroupCount))&$count=true I get:

System.InvalidOperationException: Processing of the LINQ expression '(GroupByShaperExpression: KeySelector: new GroupByWrapper{ GroupByContainer = new LastInChain{ Name = ('ProvModel'), Value = (o.ProvModel) } } , ElementSelector:(EntityShaperExpression: EntityType: OntDTO ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) )' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information. at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression) at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node) at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes) at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node) at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment) at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression) at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector) 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 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__DisplayClass12_01.b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken) at Microsoft.AspNetCore.Mvc.Infrastructure.AsyncEnumerableReader.ReadInternal[T](IAsyncEnumerable1 value)
at Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor.ExecuteAsyncEnumerable(ActionContext context, ObjectResult result, IAsyncEnumerable1 asyncEnumerable) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters() --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync() --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext) at Microsoft.AspNetCore.Builder.Extensions.MapMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

[Mhirji]

I have the same issue. Was pointed out to me that it is due to this issue with EF core... dotnet/efcore#10012.

[obohaciak]

Same issue here. On issuing

$apply=groupby((Status),aggregate(Status with countdistinct as Total))

I get exception message which reads:

System.InvalidOperationException: 'Processing of the LINQ expression '(GroupByShaperExpression:
KeySelector: new GroupByWrapper{ GroupByContainer = new LastInChain{ 
        Name = (N'Status'), 
        Value = (i.Status) 
    }
     }
, 
ElementSelector:new FlatteningWrapper<InvoiceReceived>{ 
    Source = (EntityShaperExpression: 
        EntityType: InvoiceReceived Base: Invoice
        ValueBufferExpression: 
            (ProjectionBindingExpression: Source)
        IsNullable: False
    ), 
    GroupByContainer = new LastInChain{ 
        Name = (ProjectionBindingExpression: GroupByContainer.Name), 
        Value = (ProjectionBindingExpression: GroupByContainer.Value) 
    }
     
}
)' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.'

Started after update to .NET Core 3.1.1 from .NET Core 2.2.

Packages installed:
Microsoft.AspNetCore.OData 7.3.0
Microsoft.OData.Core 7.6.2

Using MSSQL db.

Can you check if this repros with your E2E tests running against latest EF Core? I think the tests currently run against Microsoft.EntityFrameworkCore, Version=2.1.4.0

[MolallaComm]

Can we get any guidance on when this might be fixed and when the test suite might be updated to work with 3.1 instead of 2.2 like it is now? I was optimistic that PR #2069 was going to fix this also, but I see from looking that he punted and just added the compute() fixes - but didn't address the other more serious EF 3.1 compatibility problems with $apply. It is pretty much a deal breaker for anyone wanting to use odata and .net core 3.x until it is fixed.

[Mhirji]

This could be fixed, may be fixed via odata.net lib as mentioned in a response to one of the issues raised, but not updated as yet from what I have seen. It really would be great to get an update as I suspect there are a number of people running into this as a 3.x road block. Also, I suspect the following are all related:

#2030
#2003
#1952
#2019

[kosinsky]

EF Core 3.x has some breaking changes in GroupBy behaviour that made it incompatible with ODat $apply implementation.
You could still use EF Core 2.2 with ASP.NET Core 3.1 or even use EF 6. Starting from EF 6.3 it can run in .NET Core even on Linux

[MolallaComm]

2.2 is already EOL and not nearly as feature complete as 3.1 - EF6.3 has its own huge set of problems and limitations. Would be better for odata to just support the current LTS release or not support asp.net core at all IMHO to prevent people from going down the .net/efcore 3.1 route thinking it works, only to find out later after you've migrated a large project from EF6 to EFCore that odata only partially works.

I feel bad nagging here, because I see it is not really odata teams problem - efcore team is who broke it, blindly making a huge change in how LINQ queries are evaluated without thinking thru all the repercussions, although @ajcvickers did say they were going to be reaching out to odata team in the efcore bug referenced by @Mhirji above - don't know if they ever did. Maybe they have ideas on how to fix it easily as I'm sure the change they made broke a lot of other people's code also.

I've looked at the relevant odata code enough to know that all the linq expression stuff going on with group by is quite clever but hurts my head. Any chance that somebody that understands it better could just force it to call ToList() down lower so it wouldn't try to evaluate it on the server and fail - or is it more complicated than that?

[MolallaComm]

FWIW, I guess for now I will just change my controllers to be something like:

    [EnableQuery]
    public IQueryable<OntDTO> GetOntDTO()
    {
        if (Request.QueryString.Value != null && Request.QueryString.Value.Contains("$apply"))
            return db.OntDTO.ToList().AsQueryable();
        else
            return db.OntDTO;
    }

This seems to allow group by to work with EFCore 3.1 unless somebody else sees an easy way to put it lower down.

[fairking]

The query https://localhost:5001/odata/weatherforecast?$apply=groupby((summary), aggregate(temperatureC with average as Avg)) throws the error:

System.ArgumentException: Property 'System.Object Value' is not defined for type 'Microsoft.AspNet.OData.Query.Expressions.GroupByWrapper' (Parameter 'property')
   at System.Linq.Expressions.Expression.Property(Expression expression, PropertyInfo property)
   at System.Linq.Expressions.Expression.MakeMemberAccess(Expression expression, MemberInfo member)
   at LinqToDB.Linq.Builder.ExpressionBuilder.GetParameter(Expression ex, MemberInfo member)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertObjectComparison(ExpressionType nodeType, IBuildContext leftContext, Expression left, IBuildContext rightContext, Expression right)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertCompare(IBuildContext context, ExpressionType nodeType, Expression left, Expression right)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions, Boolean isNotExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving)
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.SelectBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
   at LinqToDB.Linq.CompiledTable`1.GetInfo(IDataContext dataContext)
   at LinqToDB.Linq.CompiledTable`1.Create(Object[] parameters)
   at lambda_method(Closure , Object[] )
   at LinqToDB.CompiledQuery.ExecuteQuery[TResult](Object[] args)
   at LinqToDB.CompiledQuery.Invoke[TDC,T1,T2,TResult](TDC dataContext, T1 arg1, T2 arg2)
   at LinqToDB.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetItems()
   at LinqToDB.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetEnumerator()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Linq.Enumerable.Average(IEnumerable`1 source)
   at lambda_method(Closure , IQueryRunner , IDataReader )
   at LinqToDB.Linq.QueryRunner.Mapper`1.Map(IQueryRunner queryRunner, IDataReader dataReader)
   at LinqToDB.Linq.QueryRunner.ExecuteQuery[T](Query query, IDataContext dataContext, Mapper`1 mapper, Expression expression, Object[] ps, Int32 queryNumber)+MoveNext()
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)
   at Microsoft.AspNetCore.Mvc.Formatters.NewtonsoftJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Formatters.NewtonsoftJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Formatters.NewtonsoftJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

[adomass]

Are there any plans to fix this issue soon? This seems to be one of the top things that might stop us from being able to use OData. Buying a box of beer for someone that would fix aggregate functionality for ef core 3 :)

[fairking]

@adomass Did you try to use services.AddControllers().AddNewtonsoftJson();? It seems to solve some of the issues.
I am using NHibernate and those guys fixed all issues. There is going to be a new release with fully working odata support. nhibernate/nhibernate-core#2334 (comment)

[alimozdemir]

@fairking this one is solved my problem on EF Core 3.1

Thanks !

[kgamecarter]

Add LinqToDB nuget package.
it works.
https://stackoverflow.com/questions/51918056/aggregation-with-group-by-using-aspnet-core-odata-and-entity-framework-core

[EnableQuery]
public virtual IQueryable<T> Get()
{
    IQueryable<T> query = _db.Set<T>();
    if (Request.Query.ContainsKey("$apply"))
        query = query.ToLinqToDB();
    return query;
}

[cilerler]

@mikepizzo, can you help us to get an update, please? It is another hit that OData takes because of an update on another part of the eco-system. I mean, we were so happy that finally, the endpoint routing was working on 3.1, but now groupby and aggregation are entirely gone. Somehow we always end up with a broken OData, where it should be the first-class citizen. Thanks in advance for your help.

[hasandogu]

This issue still happens with .NET 5. Is there any update on when this can be resolved?

[Uggi]

Had really hoped that this would be addressed with .NET 5 & Odata v8 & EFCore 5 but the Apply/GroupBy/Aggregate Combo is still not working.

Unfortunately neither are the 2 proposed workarounds - the linq to db doesnt work and the previous working suggestion for me under EF Core 3.1 & OData 7.5.1 of " return db.OntDTO.ToList().AsQueryable();" (basically select * from table) no longer works either :-(

looks like I`m rolling back. this has been an issue since January. Is there any possibility of getting this addressed?

[adrua]

I has a same problem. with .NET & Odata & Ef core 5. Apply/GroupBY Don't work. Any idea for resolve a problem ?

[adrua]

Resolved at OData/AspNetCoreOData#39

[athinadev]

Do you have an Idea, when this issue will be fixed?
We have moved from EntityFramework 6 to EntityFramework.Core 3.3.10, and we wait longer than 6 months for this issue to get fixed.

Thanks in advance

[adrua]

i'm sorry i not had idea.

[iozcelik]

I has a same problem. with .NET & Odata & Ef core 5. OData preview also has same issue.

[mikepizzo]

As noted, the issue is due to changes in EF Core, not in the OData stack.

The EF team has continued to support EF6 in .NET Core (including with the latest .NET Core OData stack) in order to support backward compatibility.

Does your solution work if you drop back to EF6 with the current stack?

[cilerler]

@mikepizzo It should, as you said, it is the issue in EF Core, and we need a bridge executive who will explain to them how they are hurting the OData community and so they should make it a priority and implement a workaround as early as possible.

[athinadev]

With EF6 $apply(groupby) work’s fine, but with Entity.Core we are getting the exception:
System.InvalidOperationException: EFProperty called with wrong property name.

We cannot rollback to EF6

Thanks in advance

[athinadev]

I have posted the Issue to the dotnet/efcore community

dotnet/efcore#23704

Could you please talk each other and find a solution.

Thanks in advance

[keatkeat87]

EF team said need wait until EF core 6, it will release on November 2021, How could we wait so long ?!
does somebody have a workaround or direction for this ?

[rezres]

@keatkeat87 I used @kgamecarter suggestion and it works well.

Add LinqToDB nuget package.
it works.
https://stackoverflow.com/questions/51918056/aggregation-with-group-by-using-aspnet-core-odata-and-entity-frameworkcore

[EnableQuery]
public virtual IQueryable<T> Get()
{
    IQueryable<T> query = _db.Set<T>();
    if (Request.Query.ContainsKey("$apply"))
        query = query.ToLinqToDB();
    return query;
}

[Reena-Patel]

@athinadev I have tried with the Microsoft.EntityFrameworkCore (6.0.0-preview.7.21378.4) group by is not working as well.

$skip=5&$apply=filter(amount gt 50)/groupby((customer_id),aggregate($count as receipt_count, amount with sum as total_amount))
&$top=15

[fairking]

@Reena-Patel NHibernate team fixed it long time ago: nhibernate/nhibernate-core#2334 (comment) . But EntityFramework as I can see still have that issue.