"The LINQ expression '...' could not be translated" errors when using /any(x: ...) to filter based on sub-lists

kfkvamme commented 2 years ago

OData Version

8.0.5-8.0.7

Description

When using something like $filter=Relation/any(r: ...) to filter on sub-lists in an endpoint that uses ODataQueryOptions.ApplyTo with v8.0.5 or newer, it always fails with an error like this:

System.InvalidOperationException: The LINQ expression 'DbSet<ReproItem>()
    .Where(r => DbSet<Relation>()
        .Where(r0 => EF.Property<long?>(r, "Id") != null && object.Equals(
            objA: (object)EF.Property<long?>(r, "Id"), 
            objB: (object)EF.Property<long?>(r0, "ItemId")))
        .Select(r0 => new RelationApiModel{ Id = r0.Id }
        )
        .ToArray() == null ? null : (bool?)DbSet<Relation>()
        .Where(r1 => EF.Property<long?>(r, "Id") != null && object.Equals(
            objA: (object)EF.Property<long?>(r, "Id"), 
            objB: (object)EF.Property<long?>(r1, "ItemId")))
        .Any(r1 => __p_0) == (bool?)True)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 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.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at AspNetCoreODataRepro.Controllers.ReproController.Get(ODataQueryOptions`1 odataQueryOptions) in C:\Users\kfk\source\repos\AspNetCoreODataRepro\AspNetCoreODataRepro\Controllers\ReproController.cs:line 41
   at lambda_method5(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   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 Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS
=======
Accept: */*
Connection: keep-alive
Host: localhost:7226
User-Agent: PostmanRuntime/7.29.0
Accept-Encoding: gzip, deflate, br
Authorization: Bearer eyJhbGciOiJSUzI1NiIsImtpZCI6Ijg2OGYzZTk2NjEwMjQzNjg5ZDZjMzhjOTg4MzY0YTFlIiwidHlwIjoiSldUIn0.eyJuYmYiOjE2NDQ1NjU1MzQsImV4cCI6MTY0NDU2NjEzMywiaXNzIjoiaHR0cHM6Ly9zdHMtc2l0Lm13b3JrZXIuY29tIiwiYXVkIjoiaHR0cHM6Ly9zdHMtc2l0Lm13b3JrZXIuY29tL3Jlc291cmNlcyIsImNsaWVudF9pZCI6Ik1XLUNsaWVudC1IU0VRIiwic3ViIjoiOWUyNWE0MDYtYWExNS00ZjVjLTliNmUtZGI5ZTY5YjM1YmJiIiwiYXV0aF90aW1lIjoxNjQ0NTY1NTM0LCJpZHAiOiJsb2NhbCIsImFzOmV4dGVybmFsX3N5c3RlbV9pZCI6IjIyIiwiYXM6Y2xpZW50X2lkIjoiTVctQ2xpZW50LUhTRVEiLCJuYW1lIjoia2dhQG13b3JrZXIubm8iLCJqdGkiOiI0MjkzNEI0Q0MyOEYxQUM0MDMyQ0VBRUY2Nzk5MEEyOSIsImlhdCI6MTY0NDU2NTUzNCwic2NvcGUiOlsiYXBpIiwib3BlbmlkIiwicHJvZmlsZSIsIm9mZmxpbmVfYWNjZXNzIl0sImFtciI6WyJwd2QiXX0.InXkJsGr9btJxcyDYdQvqX5iTrb1996QK2T6p_nmRurYj2GWOhJ3tVfrrl55DzpJTCHBLxoJSoZO6fyuEsQigT_K3DW7PMTUPfpxnvh2Q1F9-erGOE1KSax1h25-P9c0afsnfKqiAfySRxtzurAZZt-2oRfMRBGi4fCKfoGi6aGNmc-58_2ycieo0bBF4T621JZqGksMv6dMACydRKsDaDZqqvyyO-n-SOVh2awDpVAZG3BiLgdJz2ZmkBGn7CG030Auxgs1yKg_4FhIn0Ttp9HjS3iSuyEtieGMxsXaA7JEEE-5Mxl_MR1PRhXGwfradccA9VMeW-5KOfjP92CcAQ
Cache-Control: no-cache
mw-company-id: 1042
Postman-Token: b8b5d4b9-a71b-410c-9ef1-686f2d9479f9

In version 8.0.4 and previous versions, this works fine.

Reproduction

Here's a simple minimal project reproducing the issue: AspNetCoreODataRepro.zip

Steps

Create a database
Set the SQL server connection string in Program.cs
Run Updata-Database to create the tables
Run the project
Execute a GET request to https://localhost:7226/Repro?$filter=Relations/any(r: 1 eq 1) using Postman or a similar tool, and you should see an error similar to the one shown above

xuzhg commented 2 years ago

@kfkvamme It seems it's a regression change since 8.0.5.

As a workaround, you can change your codes to input a query settings.

query = (IQueryable<ReproItemApiModel>)odataQueryOptions.ApplyTo(query, new ODataQuerySettings { HandleNullPropagation = HandleNullPropagationOption.False });

xuzhg commented 2 years ago

@kfkvamme If possible, please help verify the latest nightly package.

kfkvamme commented 2 years ago

@kfkvamme If possible, please help verify the latest nightly package.

I tested the 8.0.8 release, and as far as I can tell from a quick test, that appears to fix the issue.

OData / AspNetCoreOData