Prevent index created by convention on many-to-many table

Question

In the configuration below, EF creates an index on SyntaxId by convention. Since I have a composite primary key (serves as index) and no identity column, I do not think this convention-created index on a single column is needed in a many-to-many table.

How can I prevent this convention index (b.HasIndex("SyntaxId");) from being auto-created?

public class SoftwareSyntaxTypeConfiguration : BaseJunctionTypeConfiguration<SoftwareSyntax>
{
    public override void Configure(EntityTypeBuilder<SoftwareSyntax> entityTypeBuilder)
    {
        base.Configure(entityTypeBuilder);
        entityTypeBuilder.ToTable("software_syntaxes");
        entityTypeBuilder.HasKey(x => new {x.SoftwareId, x.SyntaxId});
        entityTypeBuilder.HasOne(x => x.Software)
                         .WithMany(x => x.SoftwareSyntaxes)
                         .HasForeignKey(x => x.SoftwareId);
        entityTypeBuilder.HasOne(x => x.Syntax)
                         .WithMany(x => x.SoftwareSyntaxes)
                         .HasForeignKey(x => x.SyntaxId);
    }
}

partial class FilterListsDbContextModelSnapshot : ModelSnapshot
{
    protected override void BuildModel(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity("FilterLists.Data.Entities.Junctions.SoftwareSyntax", b =>
            {
                b.Property<int>("SoftwareId");

                b.Property<int>("SyntaxId");

                b.HasKey("SoftwareId", "SyntaxId");

                b.HasIndex("SyntaxId"); //TODO: prevent this from being auto-created

                b.ToTable("software_syntaxes");
            });
    }
}

Update: Adding entity classes for clarification.

public class Software
{
    public int Id { get; set; }
    public ICollection<SoftwareSyntax> SoftwareSyntaxes { get; set; }
    ...
}

public class Syntax
{
    public int Id { get; set; }
    public ICollection<SoftwareSyntax> SoftwareSyntaxes { get; set; }
    ...
}

public class SoftwareSyntax
{
    public int SoftwareId { get; set; }
    public Software Software { get; set; }

    public int SyntaxId { get; set; }
    public Syntax Syntax { get; set; }
}

For your table software_syntaxes, what is your identity column? int, auto-incrementing, non null column. Is EF guessing the identity column incorrectly, because you aren't specifying one? — TamusJRoyce, Feb 06 '18 at 15:29
I am not using an identity column as such. Since the table is explicitly for a many-to-many / junction relationship, I was using the two foreign keys of the relationship as the composite unique primary key. — Collin Barrett, Feb 06 '18 at 15:31
https://softwareengineering.stackexchange.com/questions/182103/is-a-unique-id-column-needed-in-a-many-to-many-junction-table - Just thought this was interesting. I guess multi-column id may be supported? Junction Table? Looking forward to an answer! — TamusJRoyce, Feb 06 '18 at 15:40

Collin Barrett · Accepted Answer · 2018-02-08T00:04:01.283

As it turns out, this is not officially supported yet.

A workaround, though, is to use an internal EF API. Its use carries the caveat that it could be changed without notice as it is not intended for external use.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    ((Model)modelBuilder.Model).ConventionDispatcher.StartBatch();
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    foreach (var index in entityType.GetIndexes().ToList())
        // if index is one you want to remove
            entityType.RemoveIndex(index.Properties);
}

via Arthur Vickers

Since I have all of my many-to-many entities in the same Junctions namespace, the implementation that works for me currently is:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    ((Model)modelBuilder.Model).ConventionDispatcher.StartBatch();
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    foreach (var index in entityType.GetIndexes().ToList())
        if (index.DeclaringEntityType.Name.Contains("Junctions"))
            entityType.RemoveIndex(index.Properties);
}

Prevent index created by convention on many-to-many table

1 Answers1

Linked