SQL Server: Support INCLUDE (columnA, columnB...) clause when creating indexes

[OlsonDev]

I'd like to be able to do something like:

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.Entity<Foo>()
        .HasIndex(e => new { e.ColumnA })
        .Includes(e => new { e.ColumnB, e.ColumnC })
    ;
}

which would generate a migration that would generate something like:

CREATE NONCLUSTERED INDEX IX_Foo_ColumnA_INCLUDE_ColumnB_ColumnC
ON dbo.Foo (ColumnA)
INCLUDE (ColumnB, ColumnC)
GO

[rowanmiller]

Dupe of #3748 which we closed... but we've now seen 3 distinct requests for this feature so we should re-discuss in triage.

[rabbal]

1+

[gdoron]

@rowanmiller
Is there any reason not implementing this or it's simply a matter of priority?
Index with included properties is a core feature of SQL-Server.

[DirtySmurf]

I was just trying to do something like this today, finally found this issue after a long search.

[gdoron]

@rowanmiller What's the status with this?
This issue has relatively high amount of thumbs up as well as the other linked issues.
And @jpadgeo even wrote it took him a long time to find this issue to upvote it.

Thanks!

[rowanmiller]

It's just a matter of priority. It comes after some of the other things on our Roadmap. You can workaround it at the moment by replacing the code to create indexes in your migrations with a Sql(stirng) call to create it with the INCLUDEs

[gdoron]

Thanks for the reply Rowan.
The problem with having raw DDL SQL in a migration is that you can't delete all your migrations and start fresh, which is a common pattern people do.

But I guess it's the only way currently available.

[JT-Bruch]

Just ran into this myself.. especially when attempting to create a code first database from an existing database. Would LOVE to see this added.

[arrkaye]

+1

[arrkaye]

Hi,

Is there any update on this?

I'm happy to contribute a PR if someone can point me in the right direction.

Thanks,

Ark

[ajcvickers]

@arrkaye Probably the API would be something like this:

modelBuilder.Entity<User>()
    .HasIndex(e => e.Prop1)
    .Include(e => e.Prop2);

That is, add an Include method to IndexBuilder.

The included properties would then be stored on the IIndex metadata, either as annotations or as a first-class part of the metadata. This would then flow into the MigrationsModelDiffer, and would end up in the Migrations model, which would also need updating with either annotations or first class metadata. That in turn would be used by Migrations to generate SQL.

@divega @bricelam @anpete @smitpatel @AndriySvyryd Any other thoughts on this? Should this be relational-only, or part of core indexes? If in core, annotations or first-class?