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

Solution 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

Solution 2

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

Solution 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.

Solution 4

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.

View more solutions

Comments

[Subgurim]

Thanks Viktor! It works perfectly for me. On the other hand "anonymousArguments" are provider dependant, and SQL Server just ignore it.

[kartal]

The Create index solution doesn't work I prefer you remove it because it confused me in the beginning. Thanks Viktor

[Dashu]

Really too bad that the anonymousArguments approach didn't work. There seems to be a lack of documentation around it as well. What values are supported etc.

[Rudey]

If your table name happens to be a reserved word, you'll have to escape it, e.g. tableName = "dbo.\"User\""; (at least for SQL Server)

[Rudey]

Also, in my case I had to remove an existing unique index, which was named IX_{UniqueColumn}. (So no UQ_ prefix.)

[Ricardo França]

Nice! Don't forget to create a downgrade too, ok.

[Eitan K]

I get the error: Incorrect syntax near the keyword 'WHERE'. CREATE UNIQUE NONCLUSTERED INDEX UK_InsUser_Alt ON dbo.InstitutionUser(AlternateId) WHERE AlternateId IS NOT NULL;

[Lawrence]

I'm on EF core and receiving The name 'Sql' does not exist in the current context when trying this.

[theh2o]

.IsUnique() seems to be missing from the statement above

[Gert Arnold]

This question is about EF6 classic (not core). Moreover, you're repeating this answer.