String properties with indexes are compared case insensitively on SQL Server

[GeorgDangl]

As the title states, we've had an endpoint in our ASP.NET Core Web API project covered by an integration test. The test itself was pretty simply, we're issuing a PUT request to update some database model. The failing tests tried to set the name to all uppercase and all lowercase via string.ToUpperInvariant() and string.ToLowerInvariant(). The tests started failing after I did the update to .NET 8 with EF Core 8.

Include your code

Here's a very minimal example from our code:

string originalName;
string updatedName;
using (var context = _testHelper.GetNewDanglIdentityDbContext())
{
    var dbModel = await context.OneClickRegistrationScenarios.SingleAsync(s => s.Id == _scenarioId);
    originalName = dbModel.Name;
    updatedName = dbModel.Name.ToUpperInvariant();
    dbModel.Name = updatedName;
    await context.SaveChangesAsync(); // No UPDATE statement issued in SQL
}

// Getting a new, fresh context
using (var scope = _testHelper.GetScope())
{
    using var context = scope.ServiceProvider.GetRequiredService<DanglIdentityDbContext>();
    var dbModel = await context.OneClickRegistrationScenarios.SingleAsync(s => s.Id == _scenarioId);
    Assert.Equal(updatedName, dbModel.Name); // Fails here
    Assert.NotEqual(originalName, dbModel.Name);
}

In the tests, we're running against MS SQL Server 2017 on Linux (in a Docker container on a tmpfs filesystem).

For the code block above, I'm getting the following log output. Which pretty clearly states that it saved zero changes.

Microsoft.EntityFrameworkCore.Infrastructure: Debug: Entity Framework Core 8.0.0 initialized 'DanglIdentityDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:8.0.0' with options: MigrationsAssembly=Dangl.Identity 
Microsoft.EntityFrameworkCore.Query: Debug: Compiling query expression: 
'DbSet<OneClickRegistrationScenario>()
    .Single(s => s.Id == ___scenarioId_0)'
Microsoft.EntityFrameworkCore.Query: Debug: Generated query execution expression: 
'queryContext => ShapedQueryCompilingExpressionVisitor.SingleAsync<OneClickRegistrationScenario>(
    asyncEnumerable: new SingleQueryingEnumerable<OneClickRegistrationScenario>(
        (RelationalQueryContext)queryContext, 
        RelationalCommandCache.QueryExpression(
            Projection Mapping:
                EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: OneClickRegistrationScenario.Id (Guid) Required PK AfterSave:Throw ValueGenerated.OnAdd, 0], [Property: OneClickRegistrationScenario.ClientRequired (bool) Required, 1], [Property: OneClickRegistrationScenario.HtmlActivationMessage (string) Required, 2], [Property: OneClickRegistrationScenario.HtmlDescription (string) Required, 3], [Property: OneClickRegistrationScenario.Name (string) Required Index MaxLength(256), 4] }
            SELECT TOP(2) o.Id, o.ClientRequired, o.HtmlActivationMessage, o.HtmlDescription, o.Name
            FROM OneClickRegistrationScenarios AS o
            WHERE o.Id == @___scenarioId_0), 
        null, 
        Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, OneClickRegistrationScenario>, 
        Dangl.Identity.Data.DanglIdentityDbContext, 
        False, 
        False, 
        True
    ), 
    cancellationToken: queryContext.CancellationToken)'
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Creating DbConnection.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Created DbConnection. (1ms).
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opening connection to database '10479797-d8a3-4a94-bb3c-e07a9b6d6414' on server 'localhost,51129'.
The thread 0xbb24 has exited with code 0 (0x0).
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opened connection to database '10479797-d8a3-4a94-bb3c-e07a9b6d6414' on server 'localhost,51129'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Creating DbCommand for 'ExecuteReader'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Created DbCommand for 'ExecuteReader' (1ms).
Microsoft.EntityFrameworkCore.Database.Command: Debug: Initialized DbCommand for 'ExecuteReader' (4ms).
Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[@___scenarioId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [o].[Id], [o].[ClientRequired], [o].[HtmlActivationMessage], [o].[HtmlDescription], [o].[Name]
FROM [OneClickRegistrationScenarios] AS [o]
WHERE [o].[Id] = @___scenarioId_0
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (9ms) [Parameters=[@___scenarioId_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [o].[Id], [o].[ClientRequired], [o].[HtmlActivationMessage], [o].[HtmlDescription], [o].[Name]
FROM [OneClickRegistrationScenarios] AS [o]
WHERE [o].[Id] = @___scenarioId_0
Microsoft.EntityFrameworkCore.ChangeTracking: Debug: Context 'DanglIdentityDbContext' started tracking 'OneClickRegistrationScenario' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Closing data reader to '10479797-d8a3-4a94-bb3c-e07a9b6d6414' on server 'localhost,51129'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: A data reader for '10479797-d8a3-4a94-bb3c-e07a9b6d6414' on server 'localhost,51129' is being disposed after spending 3ms reading results.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closing connection to database '10479797-d8a3-4a94-bb3c-e07a9b6d6414' on server 'localhost,51129'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closed connection to database '10479797-d8a3-4a94-bb3c-e07a9b6d6414' on server 'localhost,51129' (1ms).
Microsoft.EntityFrameworkCore.Update: Debug: SaveChanges starting for 'DanglIdentityDbContext'.
Microsoft.EntityFrameworkCore.ChangeTracking: Debug: DetectChanges starting for 'DanglIdentityDbContext'.
Microsoft.EntityFrameworkCore.ChangeTracking: Debug: DetectChanges completed for 'DanglIdentityDbContext'.
Microsoft.EntityFrameworkCore.Update: Debug: SaveChanges completed for 'DanglIdentityDbContext' with 0 entities written to the database.

I've tried the exact same code with another entity, an user from the ASP.NET Core Identity EF storage. There, I could change the username as expected to an all uppercase representation.

The model we're updating looks like this, the string property in question (Name) is marked as required and has a max length of 256:

public class OneClickRegistrationScenario
{
    [Required]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string HtmlDescription { get; set; }

    [Required]
    public string HtmlActivationMessage { get; set; }

    public bool ClientRequired { get; set; }

    public List<OneClickRegistrationScenarioDeveloperService> DeveloperServices { get; set; }

    public List<OneClickRegistrationScenarioClientScope> RequiredClientScopes { get; set; }

    public static void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<OneClickRegistrationScenario>()
            .HasIndex(x => x.Name)
            .IsUnique();

        builder.Entity<OneClickRegistrationScenario>()
            .Property(x => x.Name)
            .HasMaxLength(256);

        builder.Entity<OneClickRegistrationScenario>()
            .HasMany(x => x.RequiredClientScopes)
            .WithOne(y => y.OneClickRegistrationScenario);
    }
}

I couldn't find anything in the GitHub repo, except this issue which might seem like it's related, but I don't see anything in the PR that would affect the change tracker to not detect changes there. But, I'm also not very deep into the EF Core codebase:
#27526

Edit: I just took a look at the Debug view of the ChangeTracker, and there I saw that it was tracking the entity via it's Id property, and I saw the difference there:

OneClickRegistrationScenario {Id: fd0ba8c8-58d2-482b-af56-994338d723d1} Unchanged
    Id: 'fd0ba8c8-58d2-482b-af56-994338d723d1' PK
    ClientRequired: 'True'
    HtmlActivationMessage: 'OneClickRegistrationScenario01ActivationMessage'
    HtmlDescription: 'OneClickRegistrationScenario01Description'
    Name: 'AVACLOUD SAAS' Originally 'AVACloud SaaS'
  DeveloperServices: <null>
  RequiredClientScopes: <null>

Also, when I set another value, e.g. just Guid.NewGuid.ToString(), the change was saved correctly to the database.

Include provider and version information

EF Core version: 8.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system:
IDE: Visual Studio 2022 17.8.0

[ajcvickers]

Note for triage: I am able to reproduce this; having a unique index defined on the string property appears to be a necessary condition. I expect this is causing the key comparer to be used somewhere it should not.

int scenarioId;

using (var context = new SomeDbContext())
{
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();

    var entity = new Foo { Name = "UPPERlower" };
    context.Add(entity);
    await context.SaveChangesAsync();

    scenarioId = entity.Id;
}

using (var context = new SomeDbContext())
{
    var dbModel = await context.Set<Foo>().SingleAsync(s => s.Id == scenarioId);
    dbModel.Name = dbModel.Name.ToUpperInvariant();
    await context.SaveChangesAsync(); // No UPDATE statement issued in SQL
}

using (var context = new SomeDbContext())
{
    var dbModel = await context.Set<Foo>().SingleAsync(s => s.Id == scenarioId);
    Console.WriteLine(dbModel.Name);
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Foo>(b =>
        {
            b.HasIndex(x => x.Name).IsUnique();
        });
    }
}

public class Foo
{
    public int Id { get; set; }
    public required string Name { get; set; }
}

[ajcvickers]

@GeorgDangl I investigated this some more, and this is an intentional consequence of #27526, which also applies to indexes. You can get back the previous behavior by setting your own value comparer. For example:

        var comparer = new ValueComparer<string>(
            (l, r) => string.Equals(l, r, StringComparison.Ordinal),
            v => v.GetHashCode(),
            v => v);


        modelBuilder.Entity<Foo>(
            b =>
            {
                b.HasIndex(x => x.Name).IsUnique();
                b.Property(e => e.Name).Metadata.SetValueComparer(comparer);
            });
    }

Note for triage: it's not clear that setting the collation should change this because we don't know whether the collation is case sensitive or insensitive. It's also not clear to me that we should have done this by default for index columns, but it's also not clear that we shouldn't. For now, I think we should document as a breaking change with the mitigation being the code above.

[ajcvickers]

Note from triage: filed #32546.

[ajcvickers]

Moved to docs to document as a breaking change.

[GeorgDangl]

@ajcvickers, thank you for the detailed explanation!

Sounds like it makes sense, and I think announcing it as a breaking change would be good. Maybe this could also be covered by an analyzer, since I can imagine there are many use cases for having unique indexes on case sensitive string columns.