Add Index with Include Entity Framework (Core 2.0)

Add index with inlude in EF Core 2.0 code first migrations

Advertisements

When creating indexes with code first migrations in Entity Framework Core 2.0 you can create an index on a table by adding the following to your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity
<Table>()
        .HasIndex(t => new { rrs.Column1, rrs.Column2});
}

However when you want to include some columns in your indexes, there is no notation for it. To do that you have to make an empty migration and then add the create index by hand. You can add the code to create the index in the Up and Down part of your new migration:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
CREATE NONCLUSTERED INDEX [IX_Table_Colum1__Column2] ON [dbo].[Table]
(
    [Column1] ASC
)
INCLUDE ([Column2]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
");
        }
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropIndex("IX_Table_Colum1__Column2");
        }

When adding this kind of SQL migration code, you have to keep track of it when you want to recreate the Migrations.

Image: Sandhill Crane Silhouettes, November Sunrise, by NPS/Patrick Myers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s