EF Core Case Sensitive Primary Keys

Question

I'm using EF Core in my application for interacting with the database and using a code-first approach for generating and migrating said database.

I have entities that I am receiving from an API out of my control and I want to store those entities. The entities the API returns use case-sensitive IDs, and I would like to use those as my entity's primary key as the API ensures they will be unique and uses those IDs for relation mapping.

I have recently learned that EF and SQL default to case-insensitive and a collation needs to be set up.

In my db context's OnModelCreating, I am looking for an attribute class I created on any property to change the collation of that column.

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");
        }
    }
}

This seems to work nicely in the migration as it generates this:

APIEntityID = table.Column<string>(type: "nvarchar(450)", nullable: false, collation: "SQL_Latin1_General_CP1_CS_AS")

Seeming to set the column as case-sensitive. However, whenever I try to add an entity with a similar PK only different by case, doing dbContext.DbSet<entity>.Add(entityToAdd) throws an exception saying it cannot add the entity because it's already been added to another instance.

However, I am 99% certain that there is no other instance this entity was added to as it doesn't make since to have happened and it doesn't show up in my logs. Additionally, it always fails on the first occurrence of a "duplicate" PK only different by case.

What am I missing to get EF to properly use case sensitivity?

Answer 1

It looks like EF Core doesn't change its comparison based on what the database is set to use. So it's necessary to change the case sensitivity of the database AND of Entity Framework. What I had originally works to set case sensitivity for my database, but I also need to add an Ordinal string comparer to those fields in EF. All together the code now looks like below:

        protected override void OnModelCreating(ModelBuilder modelBuilder) {
            base.OnModelCreating(modelBuilder);

            // Create a case sensitive comparer for EF to use
            var caseSensitiveComparer = new ValueComparer<string>(
                (l, r) => string.Equals(l, r, StringComparison.Ordinal),
                v => v.GetHashCode(),
                v => v);
            
            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) {
                        // Set case sensitivity for DB
                        property.SetCollation("SQL_Latin1_General_CP1_CS_AS");
                        // Set case sensitivity for EF
                        property.SetValueComparer(caseSensitiveComparer );
                    }
                }
            }
        }

Found this issue to figure it out: https://github.com/dotnet/efcore/issues/27526