EF 6.1 Unique Nullable Index
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.
- Install Entity Framework, Define your DbContext, entities, conn string in app.config etc.
- Enable Migration - run in Package Manager Console '-EnableMigration'
- Create DbMigration - run in Package Manager Console 'Add-Migration MigrationName'
- In the created DbMigration class in ovverided
Upmethod 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
In EF Core you can use the HasFilter method in the fluent API to achieve what you're looking for without adding custom SQL to the migration.
builder.Entity<Table>() .HasIndex(x => x.PropertyName) .HasName("IX_IndexName") .HasFilter("PropertyName IS NOT NULL");This generates a migration like this:
migrationBuilder.CreateIndex( name: "IX_IndexName", table: "Table", columns: new[] { "PropertyName" }, filter: "PropertyName IS NOT NULL");
No, you cannot natively do it.
But I created a custom SQL generator that enables the following:
- Sort the columns in your index
ASCorDESC - Enable the use of the
WHEREkeyword
To be able to use it, you must tweak your index name only. The name is separated in 3 parts by :. The parts are:
- Index name
- Sort orders
- Where clause
If you have an index on 2 columns, need Column1 to be sorted ASC and Column2 DESC, and need a where clause, your index name would be:
var uniqueName = "UN_MyIndex:ASC,DESC:Column1 IS NOT NULL";And you simply use it like this:
Property(t => t.Column1) .HasColumnAnnotation(IndexAnnotation.AnnotationName, new IndexAnnotation(new IndexAttribute(uniqueName) { IsUnique = true, Order = 1 }));Property(t => t.Column2) .HasColumnAnnotation(IndexAnnotation.AnnotationName, new IndexAnnotation(new IndexAttribute(uniqueName) { IsUnique = true, Order = 2 }));Then, in your Configuration.cs file, add this line in your constructor:
SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());Finally, create the CustomSqlServerMigrationSqlGenerator.cs file as shown: code here.