How to create a Unique Index with a Where clause with Entity Framework

Question

I want to create a UNIQUE INDEX with a WHERE condition using Entity Framework.

public class Person {
    public string Name { get; set; }    

    public bool Expired { get; set; } 
}

The condition is that the Name is unique only when "Expired" is false.

The INDEX would look like this:

CREATE UNIQUE INDEX [IX_Person]
    ON Person(Name)
    WHERE [Expired] = 0;

Is there some way to write this unique index with code-first and not having to execute this SQL in a migration?

I can write this:

[Index("IX_Expired", IsUnique = true)]
public bool Expired { get; set; } 

But I don't find a way to specify the "WHERE" part.

Answer 1

In case anyone comes here looking to do this in Entity Framework core.... you can now do it!

e.g. in your MigrationContext:

   builder.Entity<MyEntity>()
        .HasIndex(x => x.MyField)
        .HasFilter($"{nameof(MyEntity.MyField)} IS NOT NULL")
        .IsUnique();

Answer 2

Is there some way to write this unique index with code-first and not having to execute this SQL in a migration?

No. EF can create basic indexes only.

EG for SQL Server, Filtered indexes, indexes with included columns, partitioned indexes, indexes on particular filegroups, indexes with row or page compression, Columnstore indexes, heap tables, memory-optimized tables, etc, can only be created using TSQL.