Efcore: Feature Request: Specifying Included Columns for an Index

[eriksendc]

Hi there,

Thanks again for the help with specifying an index using code first.

One thing that's commonly necessary in real world situations is a 'covering index', where you have the main columns that the RDBMS uses to search (seek), but then additional data is included in the index to prevent the RDBMS from having to go to the heap table to do an extra lookup. In SQL Server this list of columns goes after the INCLUDE keyword:

CREATE INDEX IX_SomeObject_SomeDateTimeProperty ON dbo.SomeObject (SomeDateTimeProperty) INCLUDE (SomeStringProperty)

I'm sure you know all this. You guys are gurus! :) Anyways, I understand that to target different RDBMS's you don't want to throw every feature of the RDBMS into EF, but I think that the concept of covering indexes is fairly mature and supported by all major RDBMS's in use today.

Please accept this as a long-term suggestion for EF7.

Thanks and regards,
-Brian Eriksen

PS - When someone gives this a read, if there's a good resource that calls out how you deal with DB objects that EF doesn't handle, it'd be great to be directed to that. I'm transitioning to code first from database first, and I'm also used to having a database project to manage all DDL. Thanks so much in advance!

[ErikEJ]

Why not use a SSDT project in Visual Studio to manage all your database objects?

[divega]

Another possible answer to this is to use the Sql() method in your migrations to create the objects EF doesn't know about.

[rowanmiller]

We discussed in triage and concluded that for the moment using Sql(...) to create the index is the right approach. If we see a significant number of folks wanting to be able to specify included columns in the model then we may reconsider adding it to the core model.

[eriksendc]

Thanks for considering this and providing me with direction.

-Brian

On Nov 17, 2015, at 4:37 PM, Rowan Miller [email protected] wrote:

We discussed in triage and concluded that for the moment using Sql(...) to create the index is the right approach. If we see a significant number of folks wanting to be able to specify included columns in the model then we may reconsider adding it to the core model.

—
Reply to this email directly or view it on GitHub.

[gdoron]

@rowanmiller
I need it too.
It can be written as an extension method to IndexBuilder.

[lcalabrese]

+1 being able to write this in the fluent API and have the migration builder automatically pick up when I change something would be a really useful time-saver.

[JanEggers]

+1 i need that too,

done it with sql during migration before.

but the problem is on each major release i wipe migrations and recreate a new initial migration to clean things up. on doing so i ofcourse lost all the manual index optimization that was done with sql migration rather than attributes and generated migration

[etrafiee]

+1 We are looking forward to have this feature as well

[munozjoseluis]

+1 I need that too, I'll have to use the method Sql.

[adyrotaru]

+1

[4tuneengineering]

+1

[jpiquot]

+1

[vinniep79]

+1

[narendrachava]

+1

[wertzui]

+1

[SergeBerwert]

+1

[eriksteinebach]

+1

[DragosBobolea]

+1

[svallis]

+1

[DR9885]

+1

[smitpatel]

It would be useful to add 👍 reaction to top most post instead of +1 posts on the thread.

[zordark]

+1

[elmadj]

+1

[FinkoAI]

+1

[smitpatel]

based on original note when closing this issue

We discussed in triage and concluded that for the moment using Sql(...) to create the index is the right approach. If we see a significant number of folks wanting to be able to specify included columns in the model then we may reconsider adding it to the core model.

Re-opening to re-consider based on responses so far.

[ajcvickers]

Based on feedback we have moved this to the backlog. We're not currently planning on working on it for 2.1 release, but we would consider a well written community PR.

[divega]

@smitpatel @ajcvickers I actually think this issue should have remained closed and we should direct customers to https://github.com/aspnet/EntityFrameworkCore/issues/4846, which is about the same thing and has more votes. See https://github.com/aspnet/EntityFrameworkCore/issues/4846#issuecomment-199456106.

[divega]

Closing as a duplicate of #4846.