How to create conditional unique constraint using EF code first approach?

Question

I need to add unique constraint on column allowing null which can be achieved using the following query:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

How can I achieve it using Entity Framework code first approach ?

Answer 1

You could use a migration to add the index:

public partial class CreateDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE UNIQUE NONCLUSTERED INDEX ourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;");
    }
}