EF6 preventing not to create Index on Foreign Key

Question

I'm using EF6 code first approach to create database. When i add migration and update database it always create Non-cluster Index for every foreign key in the table by default.

My Question: Is there any global setting for EF6 to not create Non-Cluster indexon foreign key ?

I have search and found the following solutions

Solution 1: Remove index line from migration before updating database

Solution 1 not suits me because i have a lot of tables and my db is already created. Manually remove index creation line takes much much time.

Moreover i'm also using fluent api is there any option related to this issue ?

Answer 1

Well, I think this might have been an 'If all you have is a hammer...' kinda situation.

The answer that I gave before works (and I stand by it, because it is totally fun and awesome), but it's probably not the best way to do it.

Recently I checked all the default conventions EF uses the generate the database, and there is one that's responsible for generating the non-clustered indices on FK-s. Just remove that convention altogether, and the problem is solved:

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
   base.OnModelCreating(modelBuilder);

   // using System.Data.Entity.ModelConfiguration.Conventions;
   modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
 }

Answer 2

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.

Answer 3

After trying to use CSharpMigrationCodeGenerator I start to think in way to override the ForeignKeyIndexConvention. So I implemented the check that allows to skip adding index on Primary Key column by assuming that PK naming convention was not overrided.

public class ForeignKeyIndexConventionFix : ForeignKeyIndexConvention
{
    private const string Id = "Id";

    public override void Apply(AssociationType item, DbModel model)
    {
        if (item == null)
        {
            throw new ArgumentNullException(nameof(item));
        }
        if (item.Constraint == null)
        {
            return;
        }
        if (item.IsForeignKey)
        {
            if (IsPrimaryKeyColumn(item.Constraint))
            {
                return;
            }                
        }
        base.Apply(item, model);
    }

    private static bool IsPrimaryKeyColumn(ReferentialConstraint constraint)
    {
        IEnumerable<string> dependentColumns = constraint.ToProperties.Select(p => p.Name);

        if (dependentColumns.Count() == 1)
        {
            string dependentColum = dependentColumns.First();

            if (dependentColum.Equals(Id, StringComparison.OrdinalIgnoreCase))
            {
                return true;
            }
        }
        return false;
    }
}

Then override your DbContext class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
  modelBuilder.Conventions.Add<ForeignKeyIndexConventionFix>();
}

There was problem to debug solution - Console.Write and Debug.Write were not giving output - so I just put tracing to some text file in temp location. Maybe there is a better way..?

And the code source of original implementation helped to figure out how to get dependent column names: https://github.com/dotnet/ef6/blob/master/src/EntityFramework/ModelConfiguration/Conventions/Edm/Db/ForeignKeyIndexConvention.cs

Answer 4

With EF Core 6.0, you need to remove ForeignKeyIndexConvention form all the convention sets that includes it. Here is a working solution:

Create a new ConventionSetBuilder class:

public class CustomSqlServerConventionSetBuilder : SqlServerConventionSetBuilder, IConventionSetBuilder
{
    public CustomSqlServerConventionSetBuilder(ProviderConventionSetBuilderDependencies dependencies, RelationalConventionSetBuilderDependencies relationalDependencies,
        ISqlGenerationHelper sqlGenerationHelper) : base(dependencies, relationalDependencies, sqlGenerationHelper)
    {
    }

    public override ConventionSet CreateConventionSet()
    {
        var cs = base.CreateConventionSet();

        //ForeignKeyAddedConventions
        var foreignKeyAddedConvention = cs.ForeignKeyAddedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyAddedConvention != null)
            cs.ForeignKeyAddedConventions.Remove(foreignKeyAddedConvention);

        //ForeignKeyRemovedConventions
        var foreignKeyRemovedConventions = cs.ForeignKeyRemovedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyRemovedConventions != null)
            cs.ForeignKeyRemovedConventions.Remove(foreignKeyRemovedConventions);

        //EntityTypeBaseTypeChangedConventions
        var entityTypeBaseTypeChangedConventions = cs.EntityTypeBaseTypeChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (entityTypeBaseTypeChangedConventions != null)
            cs.EntityTypeBaseTypeChangedConventions.Remove(entityTypeBaseTypeChangedConventions);

        //KeyAddedConventions
        var keyAddedConventions = cs.KeyAddedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (keyAddedConventions != null)
            cs.KeyAddedConventions.Remove(keyAddedConventions);

        //KeyRemovedConventions
        var keyRemovedConventions = cs.KeyRemovedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (keyRemovedConventions != null)
            cs.KeyRemovedConventions.Remove(keyRemovedConventions);

        //ForeignKeyPropertiesChangedConventions
        var foreignKeyPropertiesChangedConventions = cs.ForeignKeyPropertiesChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyPropertiesChangedConventions != null)
            cs.ForeignKeyPropertiesChangedConventions.Remove(foreignKeyPropertiesChangedConventions);

        //ForeignKeyUniquenessChangedConventions
        var foreignKeyUniquenessChangedConventions = cs.ForeignKeyUniquenessChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyUniquenessChangedConventions != null)
            cs.ForeignKeyUniquenessChangedConventions.Remove(foreignKeyUniquenessChangedConventions);

        //IndexAddedConventions
        var indexAddedConventions = cs.IndexAddedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (indexAddedConventions != null)
            cs.IndexAddedConventions.Remove(indexAddedConventions);

        //IndexRemovedConventions
        var indexRemovedConventions = cs.IndexRemovedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (indexRemovedConventions != null)
            cs.IndexRemovedConventions.Remove(indexRemovedConventions);

        //IndexUniquenessChangedConventions
        var indexUniquenessChangedConventions = cs.IndexUniquenessChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (indexUniquenessChangedConventions != null)
            cs.IndexUniquenessChangedConventions.Remove(indexUniquenessChangedConventions);

        //ModelFinalizingConventions
        var modelFinalizingConventions = cs.ModelFinalizingConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (modelFinalizingConventions != null)
            cs.ModelFinalizingConventions.Remove(modelFinalizingConventions);
        return cs;
    }
}

And replace the ConventionSetBuilder within your DbContext configuration:

public partial class YourDbContext : DbContext
{
    ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        ...
        optionsBuilder.ReplaceService<IConventionSetBuilder, CustomSqlServerConventionSetBuilder>();
    }
}