Annotation to create Index Unique that accepts null with EF Code First

Asked

Viewed 90 times

0

I want to create the following Index in a table by annotation:

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

SQL source above.

The most you can do is create a Unique index, but it does not accept two nulls.

This would be possible with EF6 and Sqlserver 2012?

entity-framework-6

1 answer

1

Ricardo, unfortunately I do not think it is possible, but through Migrations it is possible to create an outline solution.

after using the commands -EnableMigration and add-migration %Migration Name%, open the newly created migration file and edit the following line within the method Up:

Sql(string.Format(@"CREATE UNIQUE NONCLUSTERED INDEX IXCU_{0}_{1}_notnull
ON {0}({1}) WHERE yourcolumn IS NOT NULL", nomeTabela, nomeColuna);

if there is already an Index with the same database name, be sure to add the following line in the method Down:

DropIndex(nomeTabela, string.Format("IXCU_{0}_{1}_notnull", nomeTabela, nomeColuna));

Now you can run the update-database.

  • This was my plan B, I had seen something like this in that post. I’m gonna run some tests here.

Browser other questions tagged entity-framework-6

You are not signed in. Login or sign up in order to post.