StackOverflow adventures: Don't create non-clustered index for foreign keys with Entity Framework Code First

Akos Nagy
Sep 20, 2017

I found an interesting question on stackoverflow:

https://stackoverflow.com/questions/46212828/ef6-preventing-not-to-create-index-on-foreign-key

OP wanted to find a way to disable index creation for foreign keys. I don't believe there is an easy way to do this; my mind turned right to custom SQL generators and scaffolders.

Using custom SQL generator

One idea would be to override the SQL generation that's responsible for setting up the database when using migrations to do nothing when it is supposed to generate non-clustered indices. Something like this:

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

And then, register it in the Configuration class of the migrations:

internal sealed class Configuration : DbMigrationsConfiguration<MyContext>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;
    this.SetSqlGenerator("System.Data.SqlClient", new NoIndexGenerator());
  }
}

Now if you generate a migration, the cs file will contain all the CreateIndex operations, but when you update the database (or script the migration to a sql script) the indices will not be created. If you want, you can add implement additional checks for indices that are to be skipped (I just skip every index that is not clustered and not unique either).

A better approach: Custom migration scaffolder

I have to admit, I don't really like this solution. The cs file contains the CreateIndex operations, but they are not created in the database. If I read a code like this, I'm like 'what the hell?'. It would be a lot more straightforward if the cs files didn't contain the CreateIndex operations either.

Well, this is absolutely feasible by using a custom migration scaffolder. Migration scaffolders are the components that generate the migration cs files. You can create your own (using the same idea as for the custom SQL generator):

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

And this has to be registered in the Configuration class as well:

internal sealed class Configuration : DbMigrationsConfiguration<MyContext>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;
    this.CodeGenerator = new NoIndexMigrationCodeGenerator();
  }
}

And if you run Add-Migration now, the indices are not part of the generated cs files either.

No Github repo this time, but you can just go ahead and copy the two files and the two additional lines from here if you need it :)

Akos Nagy