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

[clement911]

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

[roji]

Thanks @clement911, I'll look into this.

[clement911]

Will the be fixed for ef8 rtm?

[ErikEJ]

@clement911 RTM bits are baked long time ago. Fixes will come in Feb 2024 or later

[roji]

@ErikEJ I'm not sure whether the 8.0.1 will come in Feb or later at this point, there may be an earlier release.

[clement911]

Hmm. That is a big regression to let slip to ef8. That means we won't be able to upgrade to
Net 8 for ages.
Aren't you concerned people will upgrade only to encounter this regression bug?

[ajcvickers]

@clement911 The team has no control over this. We can only release when management says we can.

[clement911]

I understand but you control what's in the release, no? I'm just surprised that such a regression is allowed to be included.

[ajcvickers]

@clement911 We have to hand off the bits for the release around a month before the release goes out. Again, we have no control over this.