c# - Multi-column indexes in Entity Framework 6

Question

I am working on a legacy system using Entity Framework 6.0 and there was a new requirement to allow users to re-use a code that had been used before on a deleted record. To do this I added a nullable DeletedDate column and a unique index that combined the code and deleted date.

To achieve this I added the following code to the OnModelCreating method on the DataContext.

modelBuilder.Entity<MyEntity>().HasIndex(e => new {e.Code, e.DeletedDate}).IsUnique();

This worked well. But then we had a similar requirement where the user wanted to reuse names as well as codes and not necessarily together. So I thought I could just do this:

 modelBuilder.Entity<MyEntity>().HasIndex(e => new {e.Name, e.DeletedDate}).IsUnique();
 modelBuilder.Entity<MyEntity>().HasIndex(e => new {e.Code, e.DeletedDate}).IsUnique();

This compiles, runs and throws no errors,but when I inspect the resulting indexes on the database, the first one always loses the DeletedDate column from its definition, creating a unique index on Name only. I can add the DeletedDate column back manually in SQL Server but EF simply won't do it for me.

Is this a bug or is there a good reason why I can't do this? Perhaps more importantly, is there a way I can achieve this in EF code first?

We are using SQL Server 2019.

Answer 1

The way I fixed this was to use the Index attribute on each property.

[Index("IX_Name_DeletedDate", 1, IsUnique = true)]
public string Name { get; set;}

[Index("IX_Code_DeletedDate", 1, IsUnique = true)]
public string Code { get; set;}

[Index("IX_Code_DeletedDate", 2, IsUnique = true)]
[Index("IX_UniqueIdentity_DeletedDate", 2, IsUnique = true)]
public DateTime? DeletedDate { get; set; }

The inability to do this using fluent syntax seems to be a bug in EF 6.0