CODEWITHSUNDEEP
entity-frameworkef-code-first
carraua
carraua

Reputation: 1528

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

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.

Upvotes: 7

Views: 3882

Answers (2)

Mike Perrin
Mike Perrin

Reputation: 450

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();

Upvotes: 21

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

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.

Upvotes: 7

Related Questions