Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EF 6.1 Unique Nullable Index

In EF 6.1 using Code First you can create Indexes using Attributes in your Entities or using the fluent API along the lines of:

 Property(x => x.PropertyName)                 .IsOptional()                 .HasMaxLength(450)                 .HasColumnAnnotation("Index",                     new IndexAnnotation(new IndexAttribute("IX_IndexName") {IsUnique = true,  })); 

Is there any way to say scaffold WHERE PropertyName IS NOT NULL in the same way you would in SQL Server natively (see: https://stackoverflow.com/a/767702/52026)?

like image 514
MrEdmundo Avatar asked Jun 23 '14 08:06

MrEdmundo


People also ask

Can a nullable field be unique?

You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value.

Can a unique index be null?

Although null values represent unknown values, when it comes to indexing, a null value is treated as being equal to other null values. Therefore, if a unique index consists of a single column, only one null value is allowed-more than one null value would violate the unique constraint.

Can you index nullable fields?

By default, relational databases ignore NULL values (because the relational model says that NULL means "not present"). So, Index does not store NULL value, consequently if you have null condition in SQL statement, related index is ignored (by default).

Can unique index have multiple NULL values?

When two NULL values are different, why multiple NULL values are not allowed in a column defined as UNIQUE constraint. It is probably SQL Server's implementation of a UNIQUE index is different than other database programs and also to maintain data integrity.


1 Answers

I didn't find a way to tell EF to use this where clause but here is some workaround. Check if it fit in your case.

  1. Install Entity Framework, Define your DbContext, entities, conn string in app.config etc.
  2. Enable Migration - run in Package Manager Console '-EnableMigration'
  3. Create DbMigration - run in Package Manager Console 'Add-Migration MigrationName'
  4. In the created DbMigration class in ovverided Up method run your sql for creating of unique nullable index.

code:

// Add unique nullable index  string indexName = "IX_UQ_UniqueColumn"; string tableName = "dbo.ExampleClasses"; string columnName = "UniqueColumn";  Sql(string.Format(@"     CREATE UNIQUE NONCLUSTERED INDEX {0}     ON {1}({2})      WHERE {2} IS NOT NULL;",     indexName, tableName, columnName)); 

Note: don't forget to create a downgrade, too. Ovveride Down method and use DropIndex method inside:

DropIndex(tableName, indexName); 

Also you may need some additional code if there is already data in your database which can conflict with the unique index constraint.

NOTE: Here you can use the CreateIndex method but I couldn't manage to create the correct index with it. EF just ignore my anonymousArguments or I write them wrong. You can try it yourself and write here with your result. The syntax is as follow:

CreateIndex(     table: "dbo.ExampleClasses",     columns: new string[] { "UniqueColumn" },     unique: true,     name: "IX_UniqueColumn",     clustered: false,     anonymousArguments: new     {         Include = new string[] { "UniqueColumn" },         Where = "UniqueColumn IS NOT NULL"     }); 

5 Try to add two etries with null values for the unique column and other equal values.

Here is my demo code - Pastebin

like image 93
Viktor Bahtev Avatar answered Oct 01 '22 22:10

Viktor Bahtev