Exception thrown when using IEnumberable<T>.First() inside of IEnumberable<T>.Contains() and IQueryable<T>.Where()

[jacobmcgowan]

Bug Details and Reproduction Steps

After upgrading from Microsoft.EntityFrameworkCore.SqlServer v7.0.9 to v8.0.0 and .NET 6.0 to .NET 8.0 I started getting InvalidOperationExceptions when calling .First() for an argument in IEnumberable.Contains(). For example, something like

var blogs = await context.Blogs.Where(b => postIds.Contains(b.Posts.First().PostId)).ToListAsync();

will throw an InvalidOperationException with the following message:

The LINQ expression '__postIds_0
    .Contains(MaterializeCollectionNavigation(
        Navigation: Blog.Posts,
        subquery: DbSet<Post>()
            .Where(i => EF.Property<int?>(StructuralTypeShaperExpression: 
                EfCoreBugTest.Data.Blog
                ValueBufferExpression: 
                    ProjectionBindingExpression: EmptyProjectionMember
                IsNullable: False
            , "BlogId") != null && object.Equals(
                objA: (object)EF.Property<int?>(StructuralTypeShaperExpression: 
                    EfCoreBugTest.Data.Blog
                    ValueBufferExpression: 
                        ProjectionBindingExpression: EmptyProjectionMember
                    IsNullable: False
                , "BlogId"), 
                objB: (object)EF.Property<int?>(i, "BlogId"))))
        .AsQueryable()
        .Select(s => s.PostId)
        .First())' 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.

I have encountered this issue while upgrading an existing project using Microsoft.EntityFrameworkCore.SqlServer and was able to recreate it with a new project using Microsoft.EntityFrameworkCore.Sqlite.

Stack Trace

   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression, Boolean applyDefaultTypeMapping)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   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__67`1.MoveNext()
   at EfCoreBugTest.Program.<Main>d__0.MoveNext() in C:\Users\usjamc\source\repos\EfCoreBugTest\EfCoreBugTest\Program.cs:line 12

Workaround

Using IEnumberable.Any() instead of .Contains() works.

var blogs = await context.Blogs.Where(b => postIds.Any(id => id == b.Posts.First().PostId)).ToListAsync();

Provider and version information

EF Core version: 8.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 8.0
Operating system: Windows 10 Enterprise 21H2
IDE: Visual Studio 2022 17.8.1

[roji]

Duplicate of #32217