Entity Framework Indexing ALL foreign key columns

Question

This may be too much of an opinion-based question but here goes:

I've found an interesting quirk with Entity Framework and database migrations. It seems that whenever we create a foreign key it also creates an index on that column.

I read this SO question: Entity Framework Code First Foreign Key adding Index as well and everyone seems to say it's a great, efficient idea but I don't see how; indexing a column is very circumstance-specific. For instance, EF is indexing FKs on my table that are almost never (~1%) used for searches and are also on a source table, meaning that even when I join other tables, I'm searching the FK's linked table using it's PK...there's no benefit from having the FK indexed in that scenario (that I'm aware of).

My question:

Am I missing something? Is there some reason why I would want to index a FK column that is never searched and is always on the source table in any joins?

My plan is to remove some of these questionable indexes but I wanted to to confirm that there's not some optimization concept that I'm missing.

Answer 1

Short answer. No.

To expand slightly, at the database create time, entity framework does not know how many records each table or entity will have, nor does it know how the entities will be queried.

*In my opinion * the creation of a foreign key is more likely to be right than wrong, I had massive performance issues using a different ORM which took longer to diagnose because I thought I had read in the documentation that it behaved the same way.

You can check the Sql statement that EF produces and run it manually if you want to double check.

You know your data better than EF does, and it should work just fine if you drop the index manually.

IIRC you can create 1 way navigation properties if you use the right naming convention, although this was some time ago, and I never checked whether the index was created.

Answer 2

Change the conflict FK (Foreign Key) name in ApplicationDbContextModelSnapshot file with another one. Then add migration again. It will override to it and not gonna give error.