EF Core 8.0.1 regression: an entity with a primary key in another collation returns invalid results for trivial queries.

[vvdb-architecture]

This is a regression from EF Core 6 (i.e. it worked flawlessly in EF Core 6, and stopped working in EF Core 8.0.1):

I have a simple entity defined like this:

    public class GroupPath
    {
        public int GroupID { get; set; }

        public string Path { get; set; } = default!;
    }

The fluent code for this entity type configuration is defined like this:

    class GroupPathMap : IEntityTypeConfiguration<GroupPath>
    {
        public void Configure(EntityTypeBuilder<GroupPath> builder)
        {
            builder.Property(p => p.Path).HasMaxLength(256).IsUnicode(false).UseCollation("Latin1_General_100_CS_AS");
            builder.HasKey(p => p.Path);
            builder.Property(p => p.Group).IsRequired();
            builder.ToTable("GroupPath");
        }
    }

There is not much to see in this definition, except that the primary key Path is a string with an accent-sensitive and case-sensitive collation, which is different from the default SQL Server collation (which is case-insensitive). This is needed because its contents is some special compact encoding we use internally. The reasons are not important, but non-negotiable.

The GroupPath table contains a number of records (in our case the table has 45.381 items). The actual number of records is unimportant: what's important is that Path contains both upper- and lowercase items. Here's a snippet:

Only printable ASCII characters are used. It so happens that every lowercase entry has an uppercase equivalent. So if there is a a-, there is also a A-. With the default SQL Server collation, this would not have been possible. Hence the collation in the entity definition.

The DatabaseContext is defined in the usual way:

public partial class DatabaseContext : DbContext
{
    public DbSet<GroupPath> GroupPaths { get; set; } = default!;

    public const string DefaultSchema = "azv";

    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //Custom code conventions in OnModelCreating
        //define the default schema
        modelBuilder.HasDefaultSchema(DefaultSchema);

        modelBuilder.ApplyConfiguration(new GroupPathMap());
    }
}

When we execute the following query (_database being an instance of DatabaseContext):

var list = await _database.GroupPaths.ToListAsync();

... the resulting list contains - as expected - as many items as there are records in the GroupPath table (45.381 in our case) but none of the entries will contain the records for which Path has lowercase values. Instead the list will contain duplicate records with entries containing only uppercase values for Path. Note that these will be duplicates at the instance level. I.e. the following code snippets will write both "Duplicate key" as "Duplicate instance":

            foreach (var groupPath in list)
            {
                var duplicates = list.Where(p => p.Path == groupPath.Path).ToList();
                if (duplicates.Count > 1)
                {
                    Console.WriteLine("Duplicate key");
                }
                duplicates = list.Where(p => p == groupPath).ToList();
                if (duplicates.Count > 1)
                {
                    Console.WriteLine("Duplicate instance");
                }
            }

This should be impossible and in EF Core 6 it was. Not in EF Core 8.0.1. It is as if EF Core 8.0.1 decided to use the default SQL Server collation (which is case-insensitive) to compare the primary key values of the records it obtains from the database, and inserted the uppercase match instead: i.e. the entry with Path == A- instead of the one with a-, the entry with Path == B- instead of the one with b-.

All the above code snippets should be concatenable into a functional program with minimal effort.

Please advise.

If you insist, I'll try to make a self-contained sample. But I will have to deal with the panic this has caused first.

[ajcvickers]

Duplicate of dotnet/EntityFramework.Docs#4654

[vvdb-architecture]

Thanks, @ajcvickers. Too bad I didn't see this in the list of breaking changes. It would have saved me a couple of days of debugging.

So: it looks like you need a ValueComparer for all strings whose collation differ from the case-insensitive one EF Core 8 now uses by default.

If this isn't a bug, this issue can be closed.