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