How to Get Case Sensitive Primary Keys

[SudoWatson]

I'm using EF Core for a personal project that include getting entities from a public API and saving these entities to my database. The API returns these entities with unique IDs that are case sensitive, meaning 2 different entities can have the same ID only different by casing. Since the API already generates these unique IDs and uses them for relational information, I wanted to use these as the PK of my database entities. After about 15 hours of debugging I found out EF and SQL don't by default are case insensitive. I would like to somehow get EF to recognize these columns as case-SENSITIVE. I found collations and tried implementing them but seem to be missing some steps as EF doesn't recognize the collation.

Here I've got just a basic entity, where it's primary key should be case-sensitive (as denoted by the custom case-sensitive attribute I created)

public class CaseSensitiveEntity {
    [Key]
    [CaseSensitive]
    public string NamePK { get; set; }
}

This is the attribute, it just exists

public class CaseSensitiveAttribute : Attribute {}

Here's my DbContext. In the OnModelCreating method I'm checking if a property has the attribute and apply the case sensitive collation to it if it does.

public class TestDbContext : DbContext {
    public DbSet<CaseSensitiveEntity> CaseSensitiveEntities { get; set; }
    public TestDbContext() : base() { }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=CaseSensitive;Trusted_Connection=True;MultipleActiveResultSets=true");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);
        foreach (var entityType in modelBuilder.Model.GetEntityTypes()) {
            foreach (var property in entityType.GetProperties()) {
                // If the property is decorated with the CaseSensitive attribute then set the collation to be case sensitive
                if (property.PropertyInfo?.GetCustomAttribute<CaseSensitiveAttribute>() != null) {
                    property.SetCollation("SQL_Latin1_General_CP1_CS_AS");
                }
            }
        }
    }
}

I can tell this at least attempts to apply the collation by looking at the migration file

migrationBuilder.CreateTable(
    name: "CaseSensitiveEntities",
    columns: table => new
    {
        NamePK = table.Column<string>(type: "nvarchar(450)", nullable: false, collation: "SQL_Latin1_General_CP1_CS_AS")
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_CaseSensitiveEntities", x => x.NamePK);
    });

In my program file I try adding 2 CaseSensitiveEntities, both with the same key only different by case:

using (TestDbContext dc = new TestDbContext()) {
    CaseSensitiveEntity cse1 = new CaseSensitiveEntity() {NamePK="CaseSensitive" };
    CaseSensitiveEntity cse2 = new CaseSensitiveEntity() {NamePK="CASESensitiVE" };
    dc.CaseSensitiveEntities.Add(cse1);
    dc.CaseSensitiveEntities.Add(cse2);  // <-- This is C:\Users\standarduser\source\repos\CaseSensitive\CaseSensitive\Program.cs:line 7
}

On the final line, when attempting to add the second entity (cse2), an exception is thrown because EF thinks they have the same key:

System.InvalidOperationException: 'The instance of entity type 'CaseSensitiveEntity' cannot be tracked because another 
instance with the same key value for {'NamePK'} is already being tracked. When attaching existing entities, ensure that 
only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' 
to see the conflicting key values.'

Stack trace:

   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.ThrowIdentityConflict(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.Add(TKey key, InternalEntityEntry entry, Boolean updateDuplicate)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap`1.Add(TKey key, InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.NullableKeyIdentityMap`1.Add(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.StartTracking(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState oldState, EntityState newState, Boolean acceptChanges, Boolean modifyProperties)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState entityState, Boolean acceptChanges, Boolean modifyProperties, Nullable`1 forceStateWhenUnknownKey, Nullable`1 fallbackState)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.PaintAction(EntityEntryGraphNode`1 node)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityEntryGraphIterator.TraverseGraph[TState](EntityEntryGraphNode`1 node, Func`2 handleNode)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.AttachGraph(InternalEntityEntry rootEntry, EntityState targetState, EntityState storeGeneratedWithKeySetTargetState, Boolean forceStateWhenUnknownKey)
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.SetEntityState(InternalEntityEntry entry, EntityState entityState)
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.Add(TEntity entity)
   at Program.<Main>$(String[] args) in C:\Users\standarduser\source\repos\CaseSensitive\CaseSensitive\Program.cs:line 7

What else am I missing to be able to use case sensitive primary keys in EF core?

[ajcvickers]

@SudoWatson EF Core uses case-insensitive key comparisons by default for SQL Server. (This is new in 8.0.) You can switch case-sensitive comparisons using the steps here: https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#use-case-insensitive-string-keys. (These steps show going from case-sensitive to case-insensitive, but the same approach can be used to go from case-insensitive to case-sensitive.)

[MJWRijkers]

I think this was closed too quickly, there is definitely something going on here. I have the same problem. The funny thing is, this is on a project I'm upgrading to .NET8/EFC8 from 6. I had to specifically set collation on the model builder for EFC6 to get case sensitive keys. Now that I've upgraded to EFC8, the very same code, with the very same CS collation, gives me the 'cannot be tracked because already being tracked' error when I add entities with keys that differ only in case. This persists even if I remove the collation settings.

[ajcvickers]

@MJWRijkers Have you created an configured your own case-sensitive comparer? By default, EF uses a case-insensitive comparer for SQL Server key properties, starting with EF8. You'll need something like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var comparer = new ValueComparer<string>(
        (l, r) => string.Equals(l, r, StringComparison.Ordinal),
        v => v.GetHashCode(),
        v => v);

    modelBuilder.Entity<Blog>()
        .Property(e => e.Id)
        .Metadata.SetValueComparer(comparer);

    modelBuilder.Entity<Post>(
        b =>
        {
            b.Property(e => e.Id).Metadata.SetValueComparer(comparer);
            b.Property(e => e.BlogId).Metadata.SetValueComparer(comparer);
        });
}

[MJWRijkers]

Wait now I'm confused. In the earlier comment you said the default key comparison in EFC8 for SQL server is case sensitive, as does the linked article, but now you say it's case insensitive, which is it?

I've used the case sensitive value comparer on my key fields and that does indeed solve the problem -- EFC no longer says that key 'a' and key 'A' are the same key. But if I understand your first comment and the linked article correctly, then I should not need to do that, because the default should already be case sensitive.

[ajcvickers]

The default was case sensitive until EF8, when it changed to case insensitive.

[MJWRijkers]

Alright, in that case the observed behavior is completely normal and I simply misunderstood. I just noticed the linked article is almost a year old so probably not up to date yet.

Thanks you for your answer, even on a closed issue!