In EF Code First, the general reason why you would model a foreign key relationship is for navigability between entities. Consider a simple scenario of Country and City , with eager loading defined for the following LINQ statement:

var someQuery = 
   db.Countries
     .Include(co => co.City)
     .Where(co => co.Name == "Japan")
     .Select(...);

This would result in a query along the lines of:

SELECT *
FROM Country co
INNER JOIN City ci
  ON ci.CountryId = co.ID
WHERE co.Name = 'Japan';

Without an Index on the foreign key on City.CountryId , SQL will need to scan the Cities table in order to filter the cities for the Country during a JOIN.

The FK index will also have performance benefits if rows are deleted from the parent Country table, as referential integrity will need to detect the presence of any linked City rows (whether the FK has ON CASCADE DELETE defined or not).

TL;DR

Indexes on Foreign Keys are recommended , even if you don't filter directly on the foreign key, it will still be needed in Joins. The exceptions to this seem to be quite contrived:

• If the selectivity of the foreign key is very low, e.g. in the above scenario, if 50% of ALL cities in the countries table were in Japan, then the Index would not be useful.
• If you don't actually ever navigate across the relationship.
• If you never delete rows from the parent table (or attempt update on the PK) .

One additional optimization consideration is whether to use the foreign key in the Clustered Index of the child table (i.e. cluster Cities by Country). This is often beneficial in parent : child table relationships where it is common place to retrieve all child rows for the parent simultaneously.

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.

The accepted answer insufficiently answers the question at hand, so I'm going to give it another go.

The direct answer to the question is that: High performance database design will always prefer having indexes on Foreign Keys because they're used during the check process for inserts, updates and deletes.

When any data is being inserted, updated or deleted constraints must be checked against existing foreign keys.

The OP is solely focused on the query patterns which misses the point. Indexes in this case are dual purpose: query patterns (which are not at play in this scenario) and FK constraint maintenance.

Many production apps have suffered by doing deletes on tables with FKs that are missing corresponding indexes. The data set and cascading delete just has to be large enough.

Here's a decent resource to read further - https://www.dataversity.net/foreign-keys-and-the-delete-performance-issue/

As the article mentions,
There is a very basic, yet very common misunderstanding set by developers and beginner modelers

and unfortunately the opinionated conversation has ensued from this. There is no opinion when it comes to good database design principles that must be followed or suffer the performance consequence.

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.