10

As documented in questions like Entity Framework Indexing ALL foreign key columns, EF Core seems to automatically generate an index for every foreign key. This is a sound default for me (let's not get into an opinion war here...), but there are cases where it is just a waste of space and slowing down inserts and updates. How do I prevent it on a case-by-case basis?

I don't want to wholly turn it off, as it does more good than harm; I don't want to have to manually configure it for all those indices I do want. I just want to prevent it on specific FKs.

Related side question: is the fact that these index are automatically created mentioned anywhere in the EF documentation? I can't find it anywhere, which is probably why I can't find how to disable it?

Someone is bound to question why I would want to do this... so in the interest of saving time, the OPer of the linked question gave a great example in a comment:

We have a People table and an Addresses table, for example. The People.AddressID FK was Indexed by EF but I only ever start from a People row and search for the Addresses record; I never find an Addresses row and then search the People.AddressID column for a matching record.

pbristow
  • 1,997
  • 4
  • 26
  • 46
  • If it's just for a few specific tables, can't you just remove the code that adds the Index from your Migration? – Valuator Mar 22 '18 at 15:16
  • @Valuator sorry, I'm lost. What migration? When my app starts, it creates the database from scratch by calling `db.Database.EnsureCreated();`. The creating of the indices happens automagically under the hood, as I understand it. This is EF Core 2, btw. – pbristow Mar 22 '18 at 15:30
  • Ok, `EnsureCreated` is different from Migrations, I've never use it myself though I just know the two aren't compatible. With Migrations you can revew the underlying changes to your db before applying them. – Valuator Mar 22 '18 at 15:55
  • The FKs are indexed because you and EFCore need them to set up a relation between your `People` and `Addresses` tables. It is an optimization EF does for you. The needed space is harmless as same as for the insert and update executions. You will have more pain on reading data with joins without those indexed FKs. But if you really want to remove the indices, use code based migrations with code first and remove manually the code in migration class. **I strongly advise against doing this!** – ChW Mar 22 '18 at 16:16
  • 1
    Furthermore I will recommend to read the following [MS doc about EnsureCreated()](https://learn.microsoft.com/en-us/ef/core/api/microsoft.entityframeworkcore.storage.idatabasecreator). It will not update your database if you have some model changes - migrations would do it. Intresting too (even if for EF7) [EF7 EnsureCreated vs. Migrate Methods](http://thedatafarm.com/data-access/ef7-ensurecreated-vs-migrate-methods/). [Here](http://www.entityframeworktutorial.net/efcore/entity-framework-core-migration.aspx) you could find tutorials for EF and EF Core with all supported approaches. – ChW Mar 22 '18 at 16:19
  • @ChW Thanks. If you look at my example above, and the link, you'll see there are real-world, non-trivial examples where these indexes provide zero tangible benefit and only slow the app down. I have tables in my EF Core app that have 6 indexes, 4 of which will never get used, and the indexes take up 50% more space than the data itself. Those indexes mean an insert touches 7 pages on disk when it only needs to touch 3. I have no desire to debate this in comments; it's a fact, not opinion, that this feature isn't always helpful. Debate this on the other question, if you must. Thanks. – pbristow Mar 22 '18 at 17:20
  • @ChW Thanks for the 2nd comment too. My app is brand new and EnsureCreated is the logical choice. When it's actually in production and doing migrations for schema changes is appropriate, I'll go down that road. I'm very familiar with it the two choices and when to use them; thanks. – pbristow Mar 22 '18 at 17:21
  • 1
    @pbarranis Okay. Well, I would use migrations and manage the indexes by my own and would remove not needed indeces. I hope an acceptable answer will be found, because I am interested in it too. :) – ChW Mar 22 '18 at 17:42

4 Answers4

4

EF Core has a configuration option to replace one of its services.

I found replacing IConventionSetBuilder to custom one would be a much cleaner approach.

https://giridharprakash.me/2020/02/12/entity-framework-core-override-conventions/

Giridhar Prakash Yellapu
  • 127
  • 5
  • That's a lot of work but better than nothing. Thanks! – pbristow Feb 12 '20 at 19:03
  • This doesn't seen to work in EF Core. Any idea on how it might be done now? – pinkfloydx33 Feb 12 '22 at 18:26
  • Your answer pointed me in the right direction but the code is much easier now. Just add following code to your DBContext class: `protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder) { configurationBuilder.Conventions.Remove(typeof(ForeignKeyIndexConvention)); }` – GuyVdN Sep 01 '23 at 14:15
1

If it is really necessary to avoid the usage of some foreign keys indices - as far as I know (currently) - in .Net Core, it is necessary to remove code that will set the indices in generated migration code file.

Another approach would be to implement a custom migration generator in combination with an attribute or maybe an extension method that will avoid the index creation. You could find more information in this answer for EF6: EF6 preventing not to create Index on Foreign Key. But I'm not sure if it will work in .Net Core too. The approach seems to be bit different, here is a MS doc article that should help.

But, I strongly advise against doing this! I'm against doing this, because you have to modify generated migration files and not because of not using indices for FKs. Like you mentioned in question's comments, in real world scenarios some cases need such approach.

For other people they are not really sure if they have to avoid the usage of indices on FKs and therefor they have to modify migration files:

Before you go that way, I would suggest to implement the application with indices on FKs and would check the performance and space usage. Therefor I would produce a lot test data. If it really results in performance and space usage issues on a test or QA stage, it's still possible to remove indices in migration files.

Because we already chat about EnsureCreated vs migrations here for completeness further information about EnsureCreated and migrations (even if you don't need it :-)):

ChW
  • 3,168
  • 2
  • 21
  • 34
  • Thank you. I agree that doing it this way would be a total drag, error prone, and just an all-around bad idea. I'm definitely not going down this path, but my database is small and the performance impact is minimal. Sadly, I've spent more time looking into _how_ to fix this problem than it would take me to review my entire schema and list all the unnecessary FK indexes for removal (of which there are probably 50-100). Ugh. – pbristow Mar 23 '18 at 14:17
  • Has current designed changed? I've noticed that the index being created is set to be unique. If the index is for a foreign key, shouldn't the default be not unique? – Rob White Aug 23 '19 at 10:35
  • @RobWhite I realise that yours is an old comment, but perhaps someone else will find this helpful - index is set to be unique only if your FK defines a one-to-one relationship. – Marchyello Aug 13 '20 at 13:00
0

Entity Framework core 2.0 (the latest version available when the question was asked) doesn't have such a mechanism, but EF Core 2.2 just might - in the form of Owned Entity Types.

Namely, since you said:

" I only ever start from a People row and search for the Addresses record; I never find an Addresses row"

Then you may want to make the Address an Owned Entity Type (and especially the variant with 'Storing owned types in separate tables', to match your choice of storing the address information in a separate Addresses table). The docs of the feature seem to say a matching:

"Owned entities are essentially a part of the owner and cannot exist without it"

By the way, now that the feature is in EF, this may justify why EF always creates the indexes for HasMany/HasOne. It's likely because the Has* relations are meant to be used towards other entities (as opposed to 'value objects') and these, since they have their own identity, are meant to be queried independently and allow accessing other entities they relate to using navigational properties. For such a use case, it would be simply dangerous use such navigation properties without indexes (a few queries could make the database slow down hugely).

There are few caveats here though:

Turning an entity into an owned one doesn't instruct EF only about the index, but rather it instructs to map the model to database in a way that is a bit different (more on this below) but the end effect is in fact free of that extra index on People.

But chances are, this actually might be the better solution for you: this way you also say that no one should query the Address (by not allowing to create a DbSet<T> of that type), minimizing the chance of someone using it to reach the other entities with these costly indexless queries.

As to what the difference is, you'll note that if you make the Address owned by Person, EF will create a PersonId column in the Address table, which is different to your AddressId in the People table (in a sense, lack of the foreign key is a bit of a cheat: an index for querying Person from Address is there, it's just that it's the primary key index of the People table, which was there anyways). But take note that this design is actually rather good - it not only needs one column less (no AddressId in People), but it also guarantees that there's no way to make orphaned Address record that your code will never be able to access.

If you would still like to keep the AddressId column in the Addresses, then there's still one option:

  • Just choose a name of AddressId for the foreign key in the Addresses table and just "pretend" you don't know that it happens to have the same values as the PersonId :)

If that option isn't funny (e.g. because you can't change your database schema), then you're somewhat out of luck. But do take note that among the Current shortcomings of EF they still list "Instances of owned entity types cannot be shared by multiple owners", while some shortcomings of the previous versions are already listed as addressed. Might be worth watching that space as, it seems to me, resolving that one will probably involve introducing the ability to have your AddressId in the People, because in such a model, for the owned objects to be shared among many entities the foreign keys would need to be sitting with the owning entities to create an association to the same value for each.

Slawomir Brzezinski
  • 167
  • 1
  • 12
0

in the OnModelCreating override AFTER the call to

base.OnModelCreating(modelBuilder);

add:

var indexForRemoval = modelBuilder.Entity<You_Table_Entity>().HasIndex(x => x.Column_Index_Is_On).Metadata;
modelBuilder.Entity<You_Table_Entity>().Metadata.RemoveIndex(indexForRemoval);

'''

John
  • 214
  • 3
  • 14