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

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

Answer 2

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