c# - EF 6.1 Unique Nullable Index

Question

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)?

Answer 1

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