While querying IQueryable with linq subquery it fail

Question

I just want use an IQueryable fetched from EF Core to filter customers against a filter, in this case the filter is another list of customers.

After hours of search I found this and it looks works ok, then I tried do this:

List<Customer> filteredCustomers = iqueryable.Join(customersListAsFilter.Select(F => F.Id), F => F.Id, Id => Id, (T, S) => T).ToList<Customer>();

and got this cryptic error:

System.InvalidOperationException
HResult=0x80131509
Message=Processing of the LINQ expression 'DbSet .Join( outer: __p_0, inner: F => F.Id, outerKeySelector: Id => Id, innerKeySelector: (T, S) => T)' by 'NavigationExpandingExpressionVisitor' 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.
Source=Microsoft.EntityFrameworkCore

StackTrace:

at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query) at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query) 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_01.<Execute>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.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at MyProjectDir.shared.MyDbClass.Read(List1 customers) in C:\MyProjectDir\shared\MyDbClass.cs:line 35 at MyProjectDir.shared.MyDbClass.Read(Customer customer) in C:\MyProjectDir\shared\MyDbClass.cs:line 42 at WTAPP.Controllers.CustomerController.Get(Int32 Id) in C:\MyProjectDir\MyWebProj\CustomerController.cs:line 37 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync() at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

Another alternative I found was this and I tried this:

var result = q.GroupBy(target => target.Id)
              .Where(target => target.Select(T => T.Id)
              .OrderBy(Id => Id)
              .SequenceEqual(customersListAsFilter));

But in this case, I get this error:

Error CS1929
'IOrderedEnumerable' does not contain a definition for 'SequenceEqual' and the best extension method overload 'ParallelEnumerable.SequenceEqual(ParallelQuery, IEnumerable)' requires a receiver of type 'ParallelQuery'

I don't get why you have a list of Customers and then you query the db to give you all the Customers you already have. What do you need the DB to supply that you don't already have? — Caius Jard, Jan 04 '22 at 05:49

Shahzad · Accepted Answer · 2022-01-04T03:31:49.167

Assuming your iqueryable variable is an IQueryable<Customer>, and you are trying to get a subset of customers based on what you have in customersListAsFilter, you can do the following:

List<int> customersToGet = customersListAsFilter.Select(x => x.Id).ToList();
List<Customer> filteredCustomers = iqueryable.Where(x => customersToGet.Contains(x.Id)).ToList();

The generated query will be something like:

select * -- (column names)
from Customers
where Id in (1,2,3,4,5,6) -- the IDs from customersToGet

While querying IQueryable with linq subquery it fail

1 Answers1