Entity Framework Filter Index

Question

I use EF 6.1.x Code First.

I have read that an Index with Filter Expression is not supported by EF latest.

There is also no solution on SO:

EF 6.1 Unique Nullable Index

One year later, what is the working way to make a Filter Index work with Code First and DbMigrations?

CREATE UNIQUE NONCLUSTERED INDEX [IX_DefaultLanguageApplicationId] ON [dbo].[Languages]
(
    [IsDefaultLanguage] ASC,
    [ApplicationId] ASC,
)
WHERE ([IsDefaultLanguage]=(1))

Answer 1

Please note that right now EF core 2.1.X added built in support for filtered indexes via the HasFilter extension on the IndexBuilder, so a custom implementation is not required anymore.

See this for more details

Answer 2

I know that the original post referred to the 6.1 version of EF, but after some research I have found a way to add an extension method for filtered indexes to the fluent api of EF Core (1.1 version). Maybe someone will find this useful (and maybe there is a way to implement this also in older versions). I have to warn you though. As this solution uses classes from within Microsoft.EntityFrameworkCore.Migrations.Internal and Microsoft.EntityFrameworkCore.Infrastructure namespaces, it’s no guaranteed that this code will work after EF gets updated. There is a massage included in a summary of each class within these namespaces saying that

This API may change or be removed in future releases

, so you have been warned.

But to the point.

First you have to create a standard extension method for the IndexBuilder. Its main responsibility is going to be adding a new annotation with the condition to the constructed index. One will use this method afterwards with the fluent api. Lest call our annotation SqlServer:FilteredIndex.

static class FilteredIndexExtension
{
    public static IndexBuilder Filtered(this IndexBuilder indexBuilder, string condition)
    {
        indexBuilder.HasAnnotation("SqlServer:FilteredIndex", condition);

        return indexBuilder;
    }
}

Next you have to allow this annotation to be actually included inside migrations. You have to override the default behavior of SqlServerMigrationsAnnotationProvider for index builders.

class ExtendedSqlServerMigrationsAnnotationProvider : SqlServerMigrationsAnnotationProvider
{
    public override IEnumerable<IAnnotation> For(IIndex index)
    {
        var baseAnnotations = base.For(index);
        var customAnnotatinos = index.GetAnnotations().Where(a => a.Name == "SqlServer:FilteredIndex");

        return baseAnnotations.Concat(customAnnotatinos);
    }
}

Now comes the most difficult part. We have to override the default behavior of SqlServerMigrationsSqlGenerator regarding indexes.

class ExtendedSqlServerMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public ExtendedSqlServerMigrationsSqlGenerator(IRelationalCommandBuilderFactory commandBuilderFactory, ISqlGenerationHelper sqlGenerationHelper, IRelationalTypeMapper typeMapper, IRelationalAnnotationProvider annotations, IMigrationsAnnotationProvider migrationsAnnotations) : base(commandBuilderFactory, sqlGenerationHelper, typeMapper, annotations, migrationsAnnotations)
    {
    }

    protected override void Generate(CreateIndexOperation operation, IModel model, MigrationCommandListBuilder builder, bool terminate)
    {
        base.Generate(operation, model, builder, false);

        var filteredIndexCondition = operation.FindAnnotation("SqlServer:FilteredIndex");

        if (filteredIndexCondition != null)
            builder.Append($" WHERE {filteredIndexCondition.Value}");

        if (terminate)
        {
            builder.AppendLine(SqlGenerationHelper.StatementTerminator);
            EndStatement(builder);
        }
    }
}

As you can see, we are calling the base generator here, so our condition will be added at the end of it without altering it. We have to remember not to terminate the base SQL statement here (last argument passed to the base.Generate method is false). If our annotation is set we can append its value after the WHERE clause at the end of the SQL statement. After that, depending on the argument passed to this method, we can finally terminate the statement or leave it as it is.

For all those parts to work we have to replace old services with their new versions by overriding the OnConfiguring method of our DbContext.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.ReplaceService<SqlServerMigrationsAnnotationProvider, ExtendedSqlServerMigrationsAnnotationProvider>();
        optionsBuilder.ReplaceService<SqlServerMigrationsSqlGenerator, ExtendedSqlServerMigrationsSqlGenerator>();
    }

Now we can use our extension method like this:

builder.HasIndex(a => a.Identity).IsUnique().Filtered("[End] IS NULL");

It will generate migration like this:

migrationBuilder.CreateIndex(
            name: "IX_Activities_Identity",
            table: "Activities",
            column: "Identity",
            unique: true)
            .Annotation("SqlServer:FilteredIndex", "[End] IS NULL");

And after calling Script-Migration commad in Package Manager Console we will see a resulting SQL as this:

CREATE UNIQUE INDEX [IX_Activities_Identity] ON [Activities] ([Identity]) WHERE [End] IS NULL;

This method can actually be used to include any custom SQL generator into ef core fluent api. At least as long as the EF API remains the same.

Answer 3

In EF 6.1, the working way to make the this work with Code First and DbMigrations is to use the Sql method in the DbMigration class:

public partial class AddIndexes : DbMigration
{
    public override void Up()
    {
        Sql(@"CREATE UNIQUE NONCLUSTERED INDEX
             [IX_DefaultLanguageApplicationId] ON [dbo].[Languages]
             (
                [IsDefaultLanguage] ASC,
                [ApplicationId] ASC 
             )
             WHERE ([IsDefaultLanguage]=(1))");

    }

    public override void Down()
    {
        DropIndex("dbo.Languages", "IX_DefaultLanguageApplicationId");
    }
}

But I realise that you are probably asking if you can create an index using the IndexAttribute introduced in 6.1, but with an Filter - the answer to that is "No"

Almost a duplicate of: Entity Framework 6.1 - Create index with INCLUDE statement