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 ?

There is Index attribute which could be applied to the property of the entity... But unfortunately, there is no Condition property for this attribute — Hemid Abbasov
– Hemid Abbasov, Commented Feb 22, 2018 at 14:23
@HemidAbbasov yea you are right I was required to add it using migration instead of using index attribute — Simple Code
– Simple Code, Commented Feb 22, 2018 at 16:06

Steve Ford · Accepted Answer · 2018-02-22 14:34:59Z

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;");
    }
}

answered Feb 22, 2018 at 14:34

Steve Ford

7,81323 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simple Code Over a year ago

Thanks for answering my question. I think also inside down() method I should add "drop index index_name" if I want to revert my migration

Steve Ford Over a year ago

@SimpleCode yes you should add the drop index in the Down method

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related