Case-insensitive ValueComparer doesn't seem to work with Find on untracked entity-CodePudding

I have set a case-insensitive ValueComparer<string> for the primary key, but when calling Find() it returns null if the entity isn't being tracked.

The configuration of the property and its value comparer:

modelBuilder.Entity<ProductEntity>().HasKey(e => e.Name);
modelBuilder.Entity<ProductEntity>(e =>
{
    e.Property(p => p.Name).Metadata.SetValueComparer(new ValueComparer<string>(
        (s1, s2) => string.Equals(s1, s2, StringComparison.OrdinalIgnoreCase),
        s => s.ToUpper().GetHashCode(),
        s => s
    ));
});

Code example to highlight the problem:

var builder = new DbContextOptionsBuilder<ShoppingListContext>();
builder.UseSqlite(ShoppingListContextFactory.SqliteConn.Value);

var contextFirst = new ShoppingListContext(builder.Options);
contextFirst.Database.EnsureCreated();
contextFirst.Products.Add(new ProductEntity { Name = "Apple" });
contextFirst.SaveChanges();

var contextSecond = new ShoppingListContext(builder.Options);

Console.WriteLine(contextFirst.Products.Find("apple") is null);
Console.WriteLine(contextSecond.Products.Find("apple") is null);

Outputs:

False
True

CodePudding user response：

I would suspect this is by design. The ValueComparer is used internally within EF for change tracking, essentially when working with entities that are already loaded and tracked where certain data types or rules around what constitutes a change would not be otherwise detected. Where a Database is normally case-insensitive, EF would normally detect changing "Atlanta" to "atlanta" as a valid change, where you might not want to actually trigger an UPDATE in that case since it wouldn't matter as far as the database is concerned. The side effect is that fetching data from the change tracking cache may use the configured value comparer, that doesn't automatically flow through to generated SQL commands. If your database collation is case-sensitive then your querying against string values will need to accommodate for that.

Forcing case insensitivity against a database that is case sensitive needs to be done carefully since indexing on the database may or may not match what you are configuring your Linq Queries to use which can have significant performance implications.