No translation for IEnumerable<int>.AsQueryable() as source for Contains query

[joakimriedel]

Background

I would like to be able to write a query that sometimes uses a list of IDs directly queried from the database, and sometimes uses a hard coded list of IDs, based on a condition. The hard coded list is cast to IQueryable<int> using AsQueryable() to be able to send it as a parameter to a method which accepts an IQueryable<int>

Something like this trivial example, but in real life obviously the queries are more complex.

    bool condition = true;
    IQueryable<int> offerIds = condition ? new[] {1}.AsQueryable() : context.Offers.Select(o => o.Id);
    var offers = await context.Offers.Where(o => offerIds.Contains(o.Id)).ToListAsync();

This query executes successfully if condition is false, but throws a runtime exception (see below) if condition is true - not being able to translate the expression.

Workaround

By adding AsEnumerable() in the query, it works for both conditions and still executes fully server side.

    var offers = await context.Offers.Where(o => offerIds.AsEnumerable().Contains(o.Id)).ToListAsync();

SQL output for condition = true

WHERE [o].[Id] = 1

SQL output for condition = false (EF Core 3.1.8)

WHERE [o].[Id] IN (
          SELECT [o0].[Id]
          FROM [Offers] AS [o0]
      )

SQL output for condition = false (EF Core 5.0 RC1)

WHERE EXISTS (
          SELECT 1
          FROM [Offers] AS [o0]
          WHERE [o0].[Id] = [o].[Id])

Seems to work like a charm for both condition values! 👍

Bug/feature request/question

Is this a bug or feature request? Not sure, but I would be happy to be able to do this without the workaround since it's easy to forget when building queries potentially causing the application to crash during runtime based on the actual underlying type of the IQueryable<int>. It does not seem to be a regression in 5.0 rc1, have tested this code (without workaround) as far back as 2.1.

If this already supported somehow but I am missing something obvious here - then please accept this as a question.

Stack trace

An exception of type 'System.InvalidOperationException' occurred in System.Private.CoreLib.dll but was not handled in user code: 'The LINQ expression 'EnumerableQuery<int> { 1, }
    .Contains(NavigationTreeExpression
        Value: EntityReference: Offer
        Expression: o.Id)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
   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.ExpandNavigationsForSource(NavigationExpansionExpression source, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessLambdaExpression(NavigationExpansionExpression source, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessWhere(NavigationExpansionExpression source, LambdaExpression predicate)
   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.RelationalQueryTranslationPreprocessor.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__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>d__65`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at EfCoreBug.Program.<Main>d__1.MoveNext()

EF Core version: 5.0 rc1 Database provider: Microsoft.EntityFrameworkCore.SqlServer

edit: the first post had a stupid workaround, it's AsEnumerable() that makes it work since IQueryable will always test true for IEnumerable...

[Seabizkit]

this was working and its not now, Ef core 5, I am sure this was working in a version of 3.1,

if (!string.IsNullOrWhiteSpace(searchString))
  {
      // break down the search terms in to individual keywords
      string[] searchTerms = searchString.Split(' ');

      files = files.Where(x => searchTerms.All(y => x.OriginalFilename.Contains(y))); //failing on this
  }

this does not work either

if (!string.IsNullOrWhiteSpace(searchString))
    {
        // break down the search terms in to individual keywords
        string[] searchTerms = searchString.Split(' ');
        files = files.Where(x => searchTerms.AsEnumerable().All(y => x.OriginalFilename.Contains(y)));
    }

to be clear the rest looks like

if (!string.IsNullOrWhiteSpace(searchString))
  {
      // break down the search terms in to individual keywords
      string[] searchTerms = searchString.Split(' ');

      files = files.Where(x => searchTerms.All(y => x.OriginalFilename.Contains(y))); //failing on this
  }
 if (selectedSourceId != -1)
      {
          files = files.Where(x => x.SourceId == selectedSourceId);
      }

    return files
            .Include(x => x.Source)
            .Include(x => x.Template)
            .OrderByDescending(x => x.DateCreated).ToListAsync();

if i comment out the searchString block it works. but then its not doing it, used to work.

changing to something like

   foreach (string searchTerm in searchTerms)
                {
                    files = files.Where(x => EF.Functions.Like(x.OriginalFilename, $"%{searchTerm}%"));
                }

did give fruit tho

[smitpatel]

@Seabizkit - It was not working in EF Core 3.1 either if searchstring is non-empty. See #19070. Your query is not related to this issue.

[joakimriedel]

@ajcvickers friendly bump - would this be considered a candidate to move from backlog anytime soon?

[ajcvickers]

@joakimriedel This issue has no votes. I don't see us working on this any time soon.

[joakimriedel]

Ok thanks I'll see if I can do something about it myself.