EF6 preventing not to create Index on Foreign Key

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>();
 }

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

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>();
    }
}