Entity Framework Indexing All Foreign Key Columns

Entity Framework Indexing ALL foreign key columns

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.

How to stop EF Core from indexing all foreign keys

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 :-)):

• MS doc about EnsureCreated() (It will not update your database if you have some model changes - migrations would do it)
• interesting too (even if for EF7) EF7 EnsureCreated vs. Migrate Methods

EF6 preventing not to create Index on Foreign Key

I don't believe there is a simple solution to this, but I have an idea about what you could do: create a custom migration generator.

Migration generators are the components that are responsible for creating the SQL script that is run on the database from the migration code files. I assume you have SQL Server based on the screenshot. In this case, you can write a custom sql generator that simply overrides the index creation operation so that if the index is non-clustered, nothing is written to the script:

public class NoIndexGenerator : SqlServerMigrationSqlGenerator
{
  protected override void Generate(CreateIndexOperation createIndexOperation)
  {
    if (!createIndexOperation.IsClustered)
    {
      return;
    }
  }
}

Then you have to register this component in the Configuration class of the migration:

internal sealed class Configuration : DbMigrationsConfiguration<MyCtx>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;

    // Add this line to register the sql generator
    this.SetSqlGenerator("System.Data.SqlClient", new NoIndexGenerator());
  }
}

Now if you run Add-Migration, you'll have a normal migration file, with the CreateIndexOperation in it. But if you run Update-Database, the non-clustered indices will not be created. You can also check this if you run Update-Database -Script. The resulting script does not have the non-clustered indices.

If you want, you can go even higher up in the pipeline, and create a custom C# migration scaffolder. It applies the same logic as the sql generator:

internal class NoIndexMigrationCodeGenerator : CSharpMigrationCodeGenerator
{
  protected override void Generate(CreateIndexOperation createIndexOperation, IndentedTextWriter writer)
  {
    if (!createIndexOperation.IsClustered)
    {
      return;
    }
  }
}

Then, you can register it in the Configuration class like this:

internal sealed class Configuration : DbMigrationsConfiguration<MyCtx>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;

    // Add this line to register the C# code generator
    this.CodeGenerator = new NoIndexMigrationCodeGenerator();
  }
}

Now, if you run Add-Migration, the CreateIndex operations will disappear from the generated migration cs files as well.

I'd probably go with the second solution (it can be confusing for others reading your code to see that there are CreateIndex operations in the migration cs file, but not in the SQL scripts), but ultimately it is your choice :)

You can play with other properties of the createIndexOperation parameter of the Generate() methods to implement more sophisticated index filtering, if you have to.

If you want, you can also override the Generate methods that have a parameter of type DropCreateIndexOperation but since indices are dropped with a 'drop-if-exists' pattern, I don't think this is necessary.

EDIT

While the above code samples seem to work, to be fair and follow general best-practices and principles, you should probably include calls to the base methods in both generators after the if statements.

Entity Framework Code First Foreign Key adding Index as well

This is just a convention of Entity Framework. If you don't like it, then you can enable migrations on your project and change the migration to not include the foreign key. I disagree with your assertion that it is inefficient, though.

To enable database migrations do the following:

1. In the Package Manager console, type Enable-Migrations
2. In the Package Manager console, type Add-Migration InitialMigration
3. A new migration will be added to the Migrations folder, in it you will see an Up method with a few statements. Find the line that adds the foreign key and remove it.
4. In the Package Manager console, type Update-Database to apply migrations.
5. Repeat steps 2-4 for any new changes that come in.

This is assuming you do not have a database yet and are starting from scratch.

c# .NET 4.8 How to create multi column index using default foreign keys on existing entity in DatabaseContext.cs

After playing around with it I found this page that talks about foreign key relationships in entity framework. This is where I found a different way to format my code for my TableA entity that will accomplish what I need to do.

So here is the code for my TableA entity:

public class TableA : Entity<int>
{
    public bool SomeColumn1 { get; set; }
    public bool SomeColumn2 { get; set; }
    public bool SomeColumn3 { get; set; }

    [ForeignKey("TableB")]
    public int TableB_Id { get; set; }
    public virtual TableB TableB { get; set; }
        
    [ForeignKey("TableC")]
    public int TableC_Id { get; set; }
    public virtual TableC TableC { get; set; }
}

This is very similar to one approach I tried in my original post, I just had a few things not set up right.

After I added this code to my TableA entity, back in my DatabaseCOntext.cs file I was able to add this line in the OnModelCreating() method:

modelBuilder.Entity<TableA>().HasIndex(a => new { a.TableB_Id, a.TableC_Id }).IsUnique();

which is now pointed at the fields TableA.TableB_Id and TableA.TableC_Id which weren't available before.

I compiled my solution and created a new migration and it appeared like it was going to do everything I needed it to.

I compiled the solution again and ran the migrations and the columns TableA.TableB_Id and TableA.TableC_Id are still present, set up as foreign keys to their respective tables and there is a unique constraint on them that will not allow duplicate rows to be inserted.

I hope this is helpful to others. If what I did is not clear, please leave a comment and I'll do my best to clarify.

How to set name of index or foreign key explicitly on half many-to-many in migration for Entity Framework?

Well, it seems that EF is assuming you have 2 one2many relations. So one Client could only be invited to at most one meeting.

As a quick resolution you can either

1. add 2 join entities explicitly and configure the appropriate
   one2many relations. Then you have one table for Invitations and one
   for Attendance.
2. add one many2many join entity that also tracks a
   link type (Client, Meeting, LinkType) so that "invited" and
   "attended" are link types
3. Add 2 properties to Client to show EF that
   you mean this as a many2many relation:

Like so:

public class Client { 
  public Guid Id { get; set; }
  public ICollection<Meeting> InvitedTo { get; set; }
  public ICollection<Meeting> Attended { get; set; }
}

These should not show up in the clients table but as 2 separate tables. (Essentially solution 1 with implicit join entity)

---

Related Topics