Bug in SQL Server Query Generation with .Contains and a null collection

[dbrownems]

Bug description

Code like:

List<string> col = null;
var q = db.MyEntity.Where(e => col.Contains("someval"));

Generates SQL like

select * from t where a in ( SELECT [u].[value] FROM OPENJSON( null ) AS [u] )

Which fails on SQL Server with:

Msg 468, Level 16, State 9, Line 16
Cannot resolve the collation conflict between "Latin1_General_BIN2" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

This does not depend on the database collation, whether the database collation matches the server collation, or whether partial database containment is on.

This might also be a SQL Server bug, but probably best to fix it here. SQL docs say that the collation of value derives from the collation of the expression passed to OPENJSON. Not sure why it's picking up Latin1_General_BIN2.

Using a empty string literal resolves the issue, so generated code should probably look like:

select * from t where a in ( SELECT [u].[value] FROM OPENJSON( N'') AS [u] )
This was reported by a user over on reddit:

Collation issue when running web app in Docker container

Your code

using Microsoft.EntityFrameworkCore;

using (var db = new Db())
{
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();

    List<string> names = null;

    var col = db.Foo.Where(f => names.Contains(f.Name)).ToList();

}

class Db : DbContext
{
    public DbSet<Foo> Foo { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.;Database=EfTest;Trusted_Connection=True;TrustServerCertificate=true")
                      .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);
    }

}

public class Foo
{
    public int Id { get; init; }
    public string Name { get; init; }
}

Stack traces

An exception occurred while iterating over the results of a query for context type 'Db'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "Latin1_General_BIN2" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

Verbose output

fail: 1/18/2025 17:11:39.818 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)
      Failed executing DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [f].[Id], [f].[Name]
      FROM [Foo] AS [f]
      WHERE [f].[Name] IN (
          SELECT [n].[value]
          FROM OPENJSON(NULL) AS [n]
      )
fail: 1/18/2025 17:11:39.843 CoreEventId.QueryIterationFailed[10100] (Microsoft.EntityFrameworkCore.Query)
      An exception occurred while iterating over the results of a query for context type 'Db'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "Latin1_General_BIN2" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
      ClientConnectionId:818a9aee-75ef-4747-bde9-24f4438cf6e7
      Error Number:468,State:9,Class:16

EF Core version

9.0.1

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response