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

With EF 6 you can do nonclustered unique index like:

modelBuilder.Entity<T>()
                .HasIndex(index => index.Column, "idx_column_notnull")
                .IsUnique()
                .HasFilter("column IS NOT NULL")
                .IsClustered(false);

Answer 2

Basing on Viktor's answer I come up with solution creating this code automatically.

Final migration file should not use CreateIndex method but the one I named CreateIndexNullable. This method I created in DbMigrationEx which extends DbMigration

protected void CreateIndexNullable(string table, string column, string name)
{
    Sql($@"CREATE UNIQUE NONCLUSTERED INDEX [{name}] ON {table}([{column}] ASC) WHERE([{column}] IS NOT NULL);");
}

How to change migration class code?

In Configuration class which is created in Migration folder I set

CodeGenerator = new CSharpMigrationCodeGeneratorIndexNullable();

My CSharpMigrationCodeGeneratorIndexNullable class extends CSharpMigrationCodeGenerator. I'm not gonna show exact class content, I'll just present the idea. Basing on CSharpMigrationCodeGenerator content I overrode some methods. The Entity Framework project is available at https://github.com/aspnet/EntityFramework6.

To change migration class to DbMigrationEx I used method

Generate(IEnumerable<MigrationOperation> operations, string @namespace, string className)

The only thing that needs change is

WriteClassStart(
    @namespace, className, writer, "DbMigration", designer: false,
    namespaces: GetNamespaces(operations));

To change migration method to CreateIndexNullable I used method

Generate(CreateIndexOperation createIndexOperation, IndentedTextWriter writer)

You need to change line

writer.Write("CreateIndex(");

Also

WriteIndexParameters(createIndexOperation, writer);

to

writer.Write(", ");
writer.Write(Quote(createIndexOperation.Name));

But how to know if index must be nullable?

createIndexOperation paramter contains index information. I was not able to modify CreateIndexOperation creating, but its Table, Name and Columns properties could be enough to get to fields in entity class and get Index attribute which can be extended.

Answer 3

No, you cannot natively do it.

But I created a custom SQL generator that enables the following:

1. Sort the columns in your index ASC or DESC
2. Enable the use of the WHERE keyword

To be able to use it, you must tweak your index name only. The name is separated in 3 parts by :. The parts are:

1. Index name
2. Sort orders
3. 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.

Answer 4

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

Answer 5

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