Add Index with Include Entity Framework Core

When creating indexes with code first migrations in Entity Framework Core 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 =&gt; new { t.Column1, t.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