EF Migrations drops index when adding compsite index

Question

I noticed EF removed an index on a foreign key when I added a composite index with the foreign key. So I need to understand composite indexes better :)

I added the composite index using this answer and generated my EF code first migration file.

Adding composite index:

this.Property(x => x.Name)
    .HasUniqueIndexAnnotation("IX_UniqueNamePerKey", 0);
this.Property(x => x.TeacherId)
    .HasUniqueIndexAnnotation("IX_UniqueNamePerKey", 1);

Migration file:

public partial class CompositeIndex : DbMigration
{
    public override void Up()
    {
        DropIndex("dbo.Course", new[] { "TeacherId" });
        CreateIndex("dbo.Course", new[] { "Name", "TeacherId" }, unique: true, name: "IX_UniqueNamePerKey");
    }

    // omitted...
}

What I don't understand is why it needs to drop the index on my foreign key. To my knowledge a property can be used in multiple indexes without problems. So why is it dropped? Wouldn't that make joins slower?

Model:

public class Course
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int TeacherId { get; set; }
    public virtual Teacher { get; set; }
}

public class Teacher
{
    public int Id { get; set; }
    public ICollection<Course> Courses { get; set; }
}

Mapping:

public class CourseMap : EntityTypeConfiguration<Course>
{
    protected CourseMap()
        {
            // Primary key
            this.HasKey(t => t.Id);

            // Properties
            this.Property(x => x.Name)
                .IsRequired()
                // below code was added
                .HasUniqueIndexAnnotation("IX_UniqueNamePerKey", 0);
            this.Property(x => x.ForeignKeyId)
                .HasUniqueIndexAnnotation("IX_UniqueNamePerKey", 1);

            // Table & Column Mappings
            this.ToTable("Course");
        }
}

Sure, but it didn't change. I'm just adding a unique constraint to the db. — Snæbjørn, Sep 26 '14 at 08:25
It should not have been dropped, but the columns in the composite index are in the wrong order, I think — ErikEJ, Sep 26 '14 at 08:27
I'm confused. You say the entity class didn't change, but also that you added a composite index to it? — DavidG, Sep 26 '14 at 08:29
@DavidG I added the model and mapping classes for you viewing pleasure. — Snæbjørn, Sep 26 '14 at 08:33
@Snæbjørn And where is the code for the index it is trying to drop? — DavidG, Sep 26 '14 at 08:38
The full code is rather big so it would just confuse I think. I can make the names a bit more meaningful :) Wasn't intending to post the model so the names made sense for the migration file. ForeignKeyId is assigned an index by EF convensions. It's the foreign key property for the ForeignKey navigation property. — Snæbjørn, Sep 26 '14 at 08:43
@DavidG Sorry misread. The added code was this mapping: `this.Property(x => x.Name) .HasUniqueIndexAnnotation("IX_UniqueNamePerKey", 0); this.Property(x => x.ForeignKeyId) .HasUniqueIndexAnnotation("IX_UniqueNamePerKey", 1);` — Snæbjørn, Sep 26 '14 at 08:51
@Snæbjørn But where is the code for the PREVIOUS index that it is trying to remove? — DavidG, Sep 26 '14 at 09:07
@DavidG There is no code for it, it makes an index based on convensions — Snæbjørn, Sep 26 '14 at 09:49
BTW I think we're a bit off base here. The EF schema generator is pretty good, so it might not be wrong that the index is gone. I don't know enough about index to tell. Perhaps it's still indexed via the composite index and thous is still optimized just fine. The question was whether or not joins will still be fast. — Snæbjørn, Sep 26 '14 at 10:03
Join will be slower if no index on teacherId, in addition, some improvements/bug fixes were made to Migrations in EF 6.1.1. — ErikEJ, Sep 26 '14 at 10:11
I updated but to no avail. I then tried to add the index back in manually but that didn't take either :( `...HasColumnAnnotation("Index", new IndexAnnotation(new[] { new IndexAttribute("IX_TeacherId"), new IndexAttribute("IX_UniqueNamePerKey", 0)})` — Snæbjørn, Sep 26 '14 at 11:08

score 1 · Accepted Answer · answered Sep 26 '14 at 11:56

1

I've come to the conclusion that it's bug in EF.

However in my specific case a workaround is to make the foreign key first in the composite index. As the first acts as a normal index. At least if I read this correctly.

answered Sep 26 '14 at 11:56

Snæbjørn

10,322
14
65
124

EF Migrations drops index when adding compsite index

1 Answers1

Linked