Collation conflict when using the legacy SaveChanges behavior (temporary table variable)

[stojanovicn]

There is a collation issue with SaveChangesAsync when the Merge is used (ValueGeneratedOnAdd).

Code sample

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var ctx = new TestContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
var count = await ctx.KeyDefinitionEntities.Where(t => t.TenantId == "test").CountAsync();
Console.WriteLine("Count of records = " + count);

var tests = ctx.KeyDefinitionEntities.ToList();
for (var i = 0; i < 50; i++)
    if (tests.FirstOrDefault(t => t.Id == i.ToString()) != null)
        tests.First(t => t.Id == i.ToString()).TenantId = "2";
    else
        ctx.KeyDefinitionEntities.Add(new KeyDefinitionEntity { Id = i.ToString(), TenantId = "1" });

await ctx.SaveChangesAsync();

public class TestContext : DbContext
{
    public DbSet<KeyDefinitionEntity> KeyDefinitionEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(@"Server=localhost; Database=test; User=...;Password=...")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");
        modelBuilder.Entity<KeyDefinitionEntity>().Property(t => t.Id).UseCollation("SQL_Latin1_General_CP1_CI_AS");
        // Default value will force Merge statement
        modelBuilder.Entity<KeyDefinitionEntity>().Property(t => t.IsFolder).HasDefaultValue(true).ValueGeneratedOnAdd();
        modelBuilder.Entity<KeyDefinitionEntity>().HasData(new KeyDefinitionEntity { Id = "1", TenantId = "1" });
    }
}

public class KeyDefinitionEntity
{
    public string? TenantId { get; set; }

    public string Id { get; set; }

    public bool IsFolder { get; set; }
}

Error

 SELECT [t].[IsFolder] FROM [KeyDefinitionEntities] t
      INNER JOIN @inserted1 i ON ([t].[Id] = [i].[Id])
      ORDER BY [i].[_Position];
fail: 3/31/2022 15:54:33.785 CoreEventId.SaveChangesFailed[10000] (Microsoft.EntityFrameworkCore.Update)
      An exception occurred in the database while saving changes for context type 'TestContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Provider and version information

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10 Pro 21H2
IDE: Visual Studio 2022 17.1.2

[roji]

Duplicate of #7172

[roji]

Note #27372 which should make this go away for 7.0 (unless the table has triggers).

[angularsen]

Workaround: Save one parent entity at a time. It correctly saves related data in its properties.

I didn't see this workaround mentioned anywhere, but it was very helpful for us.