Webapi: Multiple Expands Query Issue throws ArgumentOutOfRangeException in core/efcore 3+ w odata 7.3

[ikemtz]

I found an issue with using multiple expand clauses. This scenario was previously working in .net core 2.2.

Assemblies affected

Microsoft.AspNetCore.OData (7.3.0)
Microsoft.EntityFrameworkCore.SqlServer (3.1.0)

Reproduce steps

To demonstrate this issue, I stood-up an environment for testing.
As an example, getting data from one to two tables, these queries work:
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=certifications
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=competencies
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=healthItems

As soon as you try to add a third or fourth table using any of the below examples, you’ll get an HTTP 500 error
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=healthItems,certifications
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=healthItems,competencies
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=certifications,competencies
https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=certifications,competencies,healthItems

Expected result

I would expect this to behave as it did in .net core 2.2; where it populate arrays for the expanded tables.

Actual result

HTTP 500 Error
Application Insights logs the exception like so:
Exception type: System.ArgumentOutOfRangeException
Exception message: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')

Exception Stack Trace:
EfCore_OData_Issue.txt

Additional detail

The source code for this project/example can be found at:
https://github.com/ikemtz/Nurser/tree/master/src
You can use the solution filter: https://github.com/ikemtz/Nurser/blob/master/src/Nurser.Employee.Services.slnf
I added some unit tests that illustrate the issue, invoking both a standard EF core query as well as the relevant OData query. These tests can be found here:
https://github.com/ikemtz/Nurser/blob/master/src/MicroServices/Employees/src/Tests/IkeMtz.NRSRx.Employees.OData.Tests/Integration/OData/MultipleExpandsTests.cs
If it makes it easier, I have a docker image for the SQL database:
docker pull ikemtz/nrsrx-employees:sql_latest

For context and to help illustrate things a bit, here is my SQL database diagram:

[ikemtz]

I have a slight update, and I'm not sure if I should open another issue. In the scenario above, where I'm including four tables; I get an entirely different error. On this query (four tables):

https://im-wa-empo-nrsr.azurewebsites.net/odata/v1/employees?$top=2&$expand=certifications,competencies,healthItems

I'm getting this error logged in Application Insights:

System.InvalidOperationException: No coercion operator is defined between types 'System.String' and 'System.Boolean'.
at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.CreateGetValueExpression(ParameterExpression dbDataReader, Int32 index, Boolean nullable, RelationalTypeMapping typeMapping, Type clrType)
at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
at System.Linq.Expressions.ExpressionVisitor.VisitLambdaT
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.Visit(Expression node, IReadOnlyList1& projectionColumns) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQueryExpression(ShapedQueryExpression shapedQueryExpression) at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression) 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.GetOrAddQueryCoreTFunc
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsyncTResult
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsyncTResult
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.<InvokeResultAsync>g__Logged|21_0(ResourceInvoker invoker, IActionResult result) 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.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication1 application)

[mmichtch]

I have run into similar issue #2017 which maybe related to this one.
I can also confirm that I was able reproduce error described in this issue.

[nikolai-mb]

I'm experiencing this as well. I'm able to reproduce the issue with both sql server and mysql connectors for entity framework.

[thegreatdane6]

+1

[TheAifam5]

I wasn't sure if that's LINQ/EFCore related, or OData specific but I reported it here:
https://github.com/dotnet/efcore/issues/14911#issuecomment-573788437

I also provided some more analysis to it.
Looks like we get that exact issue while accessing 2 lists at the same time, and another issue when you try to access more than 2 lists at the same time.

@mmichtch yes, your issue is related to this one. We have the same issue with exact same query.

[TheoBP]

@TheAifam5 I'm experiencing the same issue. I don't think it's EFCore, as I am able to run equivalent LINQ queries without any issues.

[TheoBP]

I found a workaround for it.
Adding AddEntityFrameworkProxies and UseLazyLoadingProxies fixes the issue.

I spotted that case in a example from here 05a2f2c

Hmmm, that kind of defeats the purpose of OData for me.

[hidegh]

@TheAifam5 nice spot, EF core was surely behind it based on the stacktrace provided. Wondering what's the difference between the OData generated query and the linq queries provided in the test (sample)...

@TheoBP Wondering if this error is one of the shortcomings EF (core) has/had - when accessing foreign object's ID, even if that ID is on the current object (so N:1 relation) then the foreign object is loaded lazily... Not sure if odata should consider this (it's the ORM working odd), but if we already know what we need to load, then we could use an eagerly fetched base query we execute the odata on.

[TheAifam5]

@hidegh I really dont know what and where is the real issue. I couldn't spot any critical changes in commit history of OData project, which could lead to that behavior.

I found similar issue in the EFCore repository which I linked above.

EDIT:
Sorry, that's not a workaround. For some reason I thought that fixed it for me, because the application started partially working. :/
Sorry for misleading information.

[ikemtz]

In my opinion, being forced to enable lazy loading poses an even bigger performance problem; particularly with large datasets. OData should definitely be "including" the related tables proactively based on the expand clause.

I really don't think that this is an EF core issue. Having an identical query EF Core works, and the OData query does not.

Look at this unit test for an idea of what I'm talking about:
https://github.com/ikemtz/Nurser/blob/master/src/MicroServices/Employees/src/Tests/IkeMtz.NRSRx.Employees.OData.Tests/Integration/OData/MultipleExpandsTests.cs

[hidegh]

@ikemtz well, you can't be sure you got identical queries. EF core simply throw an exception because of some comparison. Now why it is related to lazy load or how lazy load solves it, it's a mystery (until not clarified). Probably OData will need to have to solve that "extra" of EF core when accessing child.Id on the other end of the relation (N:1) - but as I mentioned, other ORMs know they don't need to hit/load that child property (only EF is architectured that "awesome" way)...

You should try to do some SQL profiling when LazyLoad is added, as we don't know the real issue and you're just assuming that the call will cause performance issue (N+1 select issue). Also I have found similar errors with different fixes, see: https://github.com/dotnet/efcore/issues/14051

NOTE on lazy load: lazy load can be bad and good, but having proxies on the relations is a must even with eager load, as that's the only way you can get exception (instead of a false null) when underlying object is not loaded.

NOTE on EF: currently on my project not OData but EF core is the showstopper, due not being able to handle simple grouping!

[thegreatdane6]

@hidegh

Now why it is related to lazy load or how lazy load solves it, it's a mystery (until not clarified).

Just for clarification @TheAifam5 and i are working on the same project and @TheAifam5 thought this fixed the issue or parts of it but it did not. I compared the versions with and without lazyloading&proxy. Both of them behave wrong.

[TheAifam5]

It throws an error inside the EFCore internals for Select expression.
The Projection inside the SelectExpression is an array, which contains all properties as ProjectionExpression from requested classes. EF Core tries to access that projection where the value is NULL (list size 66, tries to access 67)

Also found this:
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client

Found also something else, which is related to that issue but the callstack or anything else does not say it. This case does not crash inside EF core with All Exceptions and User-Unhandled Exceptions enabled, and JustMyCode disabled.

[HttpGet]
[EnableQuery(MaxExpansionDepth = 3)]
public virtual IQueryable<TEntity> Get(ODataQueryOptions<TEntity> options)
{
  var query = this.Repository.GetAll<TEntity>();
  var result = options.ApplyTo(query.AsQueryable());
  return result as IQueryable<TEntity>;
}

Exception:

System.Runtime.Serialization.SerializationException: Cannot serialize a null 'ResourceSet'.
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObject(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatterHelper.WriteToStream(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, IWebApiUrlHelper internaUrlHelper, IWebApiRequestMessage internalRequest, IWebApiHeaders internalRequestHeaders, Func`2 getODataMessageWrapper, Func`2 getEdmTypeSerializer, Func`2 getODataPayloadSerializer, Func`1 getODataSerializerContext)
   at Microsoft.AspNet.OData.Formatter.ODataOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
--- End of stack trace from previous location where exception was thrown ---
   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.AspNet.OData.Batch.ODataBatchMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

The code should be valid and we use it in some places (more complex cases), but with that exception message, will be impossible to find the issue.

[ikemtz]

@hidegh I agree, however theoretically, the queries used in my tests should be identical. Unfortunately, I'm not able to profile the queries to see the difference because the OData query doesn't even get to the point where it executes the query on SQL.

@TheAifam5, My test is still giving the same result after the changes you suggested.

[TheAifam5]

@ikemtz I edited my comment. I apologize for misleading information.

[ikemtz]

I just wanted to mention that I upgraded to the latest Microsoft.EntityFrameworkCore.SqlServer version 3.1.1, and the issue still persists.

[joshcomley]

I think my PR fixes this over on Entity Framework:

https://github.com/dotnet/efcore/pull/19623

[joshcomley]

For reference here's the issue:

https://github.com/dotnet/efcore/issues/19616

Discovered the same issue last night, fixed it this afternoon. Hopefully they can pull PR and publish an update, soon.

[TheAifam5]

Maah HERO :D I hope this fix will be released soon so that we can rebase our repository for dotnet core 3.1, since dotnet 2.2 is EOL.

[asyncoder]

Facing the same issue which blocks me to move to .NET Core 3.x

[ikemtz]

@asyncoder, there's already a fix for this thanks to @joshcomley. The current problem is the guy that needs to approve the PR in EF core, has been on vacation for the last two weeks.

[ikemtz]

I'm closing this issue as a duplicate of
https://github.com/dotnet/efcore/issues/19616