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

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.