Regression with translation of `Contains` for SqlServer

[AsgerPetersen]

We have tried upgrading our production code to EfCore8 and we are seeing several failing tests. Seemingly caused by a regression in the translation of Contains in EfCore. The mitigations mentioned in the docs dont seem to work in our case.

I have been through the other issues regaring Contains and I am not sure if this is a duplicate of any of those.

This is a simple reproducer (here is a complete reproducing project with this code)

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace ContainsOptimization
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            using (var context = new MyContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var types = new[] { PersonType.Happy, PersonType.Sad };

                var result = context.People.Where(p => types.Contains(p.Children.Any() ? PersonType.Happy : PersonType.Sad)).ToList();
                Console.WriteLine($"It worked: {result.Count}");
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Person> People { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                @"Server=localhost,14330;Database=aika;User=sa;Password=SecretPassword1234;TrustServerCertificate=True");
        }
    }

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public PersonType Type { get; set; }
        public virtual List<Child> Children { get; private set; }
    }

    public class Child
    {
        public int Id { get; set; }
        public int PersonId { get; set; }
        public Person Person { get; set; }

        public string Name { get; set; }
    }

    public enum PersonType
    {
        Happy,
        Sad
    }
}

This works nicely with EfCore7 whereas EfCore 8 rc2 throws this exception:

Unhandled exception. System.InvalidOperationException: The LINQ expression '__types_0
    .Contains(MaterializeCollectionNavigation(
        Navigation: Person.Children,
        subquery: DbSet<Child>()
            .Where(i => EF.Property<int?>(StructuralTypeShaperExpression: 
                ContainsOptimization.Person
                ValueBufferExpression: 
                    ProjectionBindingExpression: EmptyProjectionMember
                IsNullable: False
            , "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(StructuralTypeShaperExpression: 
                    ContainsOptimization.Person
                    ValueBufferExpression: 
                        ProjectionBindingExpression: EmptyProjectionMember
                    IsNullable: False
                , "Id"), 
                objB: (object)EF.Property<int?>(i, "PersonId"))))
        .AsQueryable()
        .Any() ? Happy : Sad)' 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.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.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 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.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ContainsOptimization.Program.Main(String[] args) in /Users/asger/Code/Aika/Aika-DevHelpers/efcore8contains/Program.cs:line 19

Provider and version information

EF Core version: 8.0.0-rc.2.23480.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 8.0 Operating system: OSX

[ErikEJ]

@AsgerPetersen have you tried the latest daily EF Core 8 build? https://github.com/dotnet/efcore/blob/main/docs/DailyBuilds.md - or EF Core 8.0.0 later today.

[ajcvickers]

@roji Confirmed that this still fails on latest daily build.

[AsgerPetersen]

@ErikEJ I think so and it failed. But it is my first time with daily builds, so I am not confident that I did it correctly.

[ErikEJ]

@AsgerPetersen - @ajcvickers was quick and just tested on latest daily, and it fails...

[roji]

I'll investigate this soon. In the meantime, as a workaround you can configure the SQL Server compatibility level as described here.

[AsgerPetersen]

Thank you for looking into this.

Our SQLServer runs compatibility level 160 so we can´t turn it further up. If we go down to 120 (using this description) it throws this exception:

Unhandled exception. System.InvalidOperationException: The LINQ expression '[Microsoft.EntityFrameworkCore.Query.ParameterQueryRootExpression]' could not be translated. Additional information: EF Core's SQL Server compatibility level is set to 120; compatibility level 130 (SQL Server 2016) is the minimum for most forms of querying of JSON arrays. 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.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.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 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.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ContainsOptimization.Program.Main(String[] args) in /Users/asger/Code/efcore8contains/Program.cs:line 23

[ajcvickers]

@roji This works on EF7:

info: 11/14/2023 12:28:21.642 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [p].[Id], [p].[Name], [p].[Type]
      FROM [People] AS [p]
      WHERE CASE
          WHEN EXISTS (
              SELECT 1
              FROM [Child] AS [c]
              WHERE [p].[Id] = [c].[PersonId]) THEN 0
          ELSE 1
      END IN (0, 1)

But throws on EF8 even with the compat level set:

Unhandled exception. System.InvalidOperationException: The LINQ expression '[Microsoft.EntityFrameworkCore.Query.ParameterQueryRootExpression]' could not be translated. Additional information: EF Core's SQL Server compatibility level is set to 120; compatibility level 130 (SQL Server 2016) is the minimum for most forms of querying of JSON arrays. 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.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.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 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.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ContainsOptimization.Program.Main(String[] args) in C:\local\code\repros\efcore8contains-main\efcore8contains-main\Program.cs:line 24

[roji]

This turned out to be the same as #32217: nav expansion doesn't visit the item for (queryable) Contains. Making ProcessContains visit the item makes this work:

private Expression ProcessContains(NavigationExpansionExpression source, Expression item)
{
    source = (NavigationExpansionExpression)_pendingSelectorExpandingExpressionVisitor.Visit(source);
    var queryable = Reduce(source);
    var visitedItem = Visit(item);

    return Expression.Call(QueryableMethods.Contains.MakeGenericMethod(queryable.Type.GetSequenceType()), queryable, visitedItem);
}

Here's what's going on in more detail:

• In 7.0, the Contains is Enumerable.Contains.
  • This causes us to skip Contains handling in NavigationExpandingExpressionVisitor.VisitMethodCall, going to ProcessUnknownMethod instead.
  • Importantly, ProcessUnknownMethod recursively visits the MethodCallExpression (base.VisitMethodCall(methodCallExpression)); this causes the Contains lambda argument to be processed correctly, restoring the original EntityQueryRootExpression.
  • This is later successfully picked up in translation and translated.
• In 8.0, the Contains is Queryable, as it's composed on top of the new ParameterQueryRootExpression.
  • This causes nav expansion to recognize the Contains, and call into ProcessContains.
  • Contains doesn't visit the item (this is #32217), this causes it to be incorrectly reduced to a MaterializeCollectionNavigationExpression later.

[roji]

Duplicate of #32217

[CorsairRO]

Hello Guys.

Unfortunately i have to tell you that the breaking change is NOT working like it should, even if configured properly.

1) I tested now on SQL Server 2022, so i shouldnt set any compatibility level according to release note of that change 2) I have a IQueryable that i run with a Contains in a LIST. This worked fine in all prev versions of EF Core. 3) I get this now: Failed executing DbCommand (34ms) [Parameters=[@__idList_0='[]' (Size = 4000)], CommandType='Text', CommandTimeout='300'] SELECT * FROM [ATable] AS [a] WHERE [a].[Id] IN ( SELECT [p].[value] FROM OPENJSON(@__idList_0) WITH ([value] int '$') AS [p] ) 4) My EF LINQ QUery is exactly as it should. var aList = db.ATable.Where(x=> idList.Contains(x.Id)) 5) So if it doesnt work in this scenario i wonder in which scenarios it passed any test there.

6) Setting compatibilty to 120 it works fine, but i DONT think that this can be an accepted solution to revert back ALL the queries to compat level just to overcome such a bug

[ErikEJ]

@CorsairRO The server version is not important, it is your database compatibility level that matters:

SELECT name, compatibility_level FROM sys.databases;

[roji]

@CorsairRO your question isn't related to this issue, which tracks a specific bug.

As @ErikEJ wrote, check the compatibility level configured in your database, as documented in the breaking change note. If you're sure that your compatibility level isn't the problem, please open a new issue with a runnable, minimal code sample that shows the problem happening.