efcore: EF8 - Regression on Contains query when column collation is not default DB collation

There seems to be a regression bug related to how EF8 now translates Contains with OPENJSON instead of using IN in previous versions. This applies to SQL server and SQL database and it happens when the string column uses a collation that is not the default DB collation.

I could reproduce this issue on Azure SQL Database.

I could also reproduce on a local SQL server instance, but only if the DB containment type is set to Partial.

If I set the containment type to ‘None’, then the query runs fine.

using EFRepro;
using Microsoft.EntityFrameworkCore;

var ids = new[] { "a", "b", "c" };

try
{
    using (var db = new BloggingContext())
    {
        db.Blogs.Add(new Blog { Id = Guid.NewGuid().ToString() });
        db.SaveChanges();
        var query = db.Blogs.Where(b => ids.Contains(b.Id));
        string sql = query.ToQueryString();
        var blogs = query.ToList();
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}


namespace EFRepro
{
    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(local)\sql2022;Database=Blogs;Trusted_Connection=True;TrustServerCertificate=true");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .Property(i => i.Id)
                .UseCollation("Latin1_General_100_BIN2_UTF8");
        }
    }

    public class Blog
    {
        public string Id { get; set; }
    }
}

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_BIN2_UTF8" 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()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.<Main>$(String[] args) in C:\Dev\EFRepro\EFRepro\Program.cs:line 14
ClientConnectionId:5b0ee62c-b3a5-4aa9-b1fd-3f3a904e5689
Error Number:468,State:9,Class:16

Generated sql

DECLARE @__ids_0 nvarchar(4000) = N'["a","b","c"]';

SELECT [b].[Id]
FROM [Blogs] AS [b]
WHERE [b].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] nvarchar(450) '$') AS [i]
)

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

In order to fix it, EF would have add a COLLATE clause with the name of the collation that is configured in the model. Either on the Id or Value column.

SELECT [b].[Id]
FROM [Blogs] AS [b]
WHERE [b].[Id] COLLATE Latin1_General_100_BIN2_UTF8 IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] nvarchar(450) '$') AS [i]
)


SELECT [b].[Id]
FROM [Blogs] AS [b]
WHERE [b].[Id] IN (
    SELECT [i].[value] COLLATE Latin1_General_100_BIN2_UTF8
    FROM OPENJSON(@__ids_0) WITH ([value] nvarchar(450) '$') AS [i]
)

EF Core version: 8.0.0-rc.2.23480.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 8.0 Operating system: Windows 11 IDE: VS 2022 17.8 Preview 4