Why isn’t “LINQ and SQL translation — improved translation for parameterized collections” mentioned in the breaking changes of EF CORE 10?

[ksusac]

Question

Why is the change in default behavior of .Contains() translation not mentioned under Breaking changes for EF Core 10? The same issue was noted in EF Core 8 when this functionality changed.

This change results in a runtime error, System.DivideByZeroException, when a query has a large number of parameters, approaching the 2,100 limit.

I’m sure we’re not the only ones checking the breaking-changes documentation to understand why queries that worked in EF Core 9 are now throwing exceptions.

Your code

var result = await _dbContext.SomeEntity
    .Where(d => ids.Contains(d.Id))
    .ToListAsync();

Stack traces

System.DivideByZeroException:
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor+<>c__DisplayClass33_0.<VisitIn>g__ProcessInExpressionValues|1 (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitIn (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlNullabilityProcessor.VisitIn (Microsoft.EntityFrameworkCore.SqlServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitExtension (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlNullabilityProcessor.VisitExtension (Microsoft.EntityFrameworkCore.SqlServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Process (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlNullabilityProcessor.Process (Microsoft.EntityFrameworkCore.SqlServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerParameterBasedSqlProcessor.ProcessSqlNullability (Microsoft.EntityFrameworkCore.SqlServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.RelationalParameterBasedSqlProcessor.Process (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerParameterBasedSqlProcessor.Process (Microsoft.EntityFrameworkCore.SqlServer, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.RelationalParameterBasedSqlProcessor.Process (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Internal.RelationalCommandResolverExtensions.RentAndPopulateRelationalCommand (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1+AsyncEnumerator+<InitializeReaderAsync>d__21.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=10.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)

Verbose output

EF Core version

10.0.0

Database provider

No response

Target framework

.NET 10

Operating system

No response

IDE

No response

[stevendarby]

Why is the change in default behavior of .Contains() translation not mentioned under Breaking changes for EF Core 10?

I don't think the change was intended to be breaking. It looks like you've discovered a bug. You mentioned "when a query has a large number of parameters, approaching the 2,100 limit." - are you able to pin down the exact circumstances under which this happens, and submit a minimal repro?

[ksusac]

Yeah, I guess it is a bug. Here is a minimal repo. If I were to guess, this would be the culprit:

Since different cardinalities still cause different SQLs to be generated, EF also "pads" parameter list. For example, if your ids list contains 8 values, EF generates SQL with 10 parameters:

An error is thrown if the number of elements in the list is between 2072 and 2101.

using Microsoft.EntityFrameworkCore;

List<int> list = [];

for (int i = 0; i < 2097; i++)
{
    list.Add(i);
}

using var dbContext = new BloggingContext();

var blogs = await dbContext.Set<Blog>()
    .Where(b => list.Contains(b.BlogId))
    .ToListAsync();

class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=EFCore10Demo;Trusted_Connection=True;");
    public DbSet<Blog> Blogs { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string? Url { get; set; }
}

[ksusac]

I guess it got crazy 😄

/// <inheritdoc />
    protected override int CalculateParameterBucketSize(int count, RelationalTypeMapping elementTypeMapping)
        => count switch
        {
            <= 5 => 1,
            <= 150 => 10,
            <= 750 => 50,
            <= 2000 => 100,
            <= 2070 => 10, // try not to over-pad as we approach that limit
            <= MaxParameterCount => 0, // just don't pad between 2070 and 2100, to minimize the crazy
            _ => 200,
        };

[PhyxionNL]

I have the same issue with queries that worked without issues in 9.0.

[ksusac]

I have created bug issue for this: dotnet/efcore#37151.

I still feel this change should have more visibility, so i will keep this open for discussion.

[stevendarby]

I suspect the fix is to change the 0 to a 1. Hopefully the team will be able to implement a proper fix soon, but for the time being here is a workaround demonstrated through the repro @ksusac gave

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Linq.Expressions;

List<int> list = [];

for (int i = 0; i < 2097; i++)
{
    list.Add(i);
}

using var dbContext = new BloggingContext();
dbContext.Database.EnsureCreated();

var blogs = await dbContext.Set<Blog>()
    .Where(b => list.Contains(b.BlogId))
    .ToListAsync();

class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
        .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=EFCore10Demo;Trusted_Connection=True;")
        .ReplaceService<IRelationalParameterBasedSqlProcessorFactory, CustomSqlServerParameterBasedSqlProcessorFactory>();

    public DbSet<Blog> Blogs { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string? Url { get; set; }
}

internal class CustomSqlServerParameterBasedSqlProcessorFactory(
    RelationalParameterBasedSqlProcessorDependencies dependencies,
    ISqlServerSingletonOptions sqlServerSingletonOptions)
    : SqlServerParameterBasedSqlProcessorFactory(dependencies, sqlServerSingletonOptions)
{
    public override RelationalParameterBasedSqlProcessor Create(RelationalParameterBasedSqlProcessorParameters parameters)
     => new CustomSqlServerParameterBasedSqlProcessor(dependencies, parameters, sqlServerSingletonOptions);
}

internal class CustomSqlServerParameterBasedSqlProcessor(
    RelationalParameterBasedSqlProcessorDependencies dependencies,
    RelationalParameterBasedSqlProcessorParameters parameters,
    ISqlServerSingletonOptions sqlServerSingletonOptions)
    : SqlServerParameterBasedSqlProcessor(dependencies, parameters, sqlServerSingletonOptions)
{
    protected override Expression ProcessSqlNullability(Expression selectExpression, ParametersCacheDecorator Decorator)
        => new CustomSqlServerSqlNullabilityProcessor(Dependencies, Parameters, sqlServerSingletonOptions)
            .Process(selectExpression, Decorator);
}

public class CustomSqlServerSqlNullabilityProcessor(
    RelationalParameterBasedSqlProcessorDependencies dependencies,
    RelationalParameterBasedSqlProcessorParameters parameters,
    ISqlServerSingletonOptions sqlServerSingletonOptions)
    : SqlServerSqlNullabilityProcessor(dependencies, parameters, sqlServerSingletonOptions)
{
    protected override int CalculateParameterBucketSize(int count, RelationalTypeMapping elementTypeMapping)
    {
        var size = base.CalculateParameterBucketSize(count, elementTypeMapping);
        return size == 0 ? 1 : size;
    }
}

[roji]

Regardless of the DivisionByZero bug flagged above (dotnet/efcore#37151), I agree that it makes sense to add a mention in the 10.0 breaking change page.

Keeping this issue to track the doc change (moved this to the docs repo), please keep discussion on the bug in dotnet/efcore#37151.