3

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");
        }
}
17
  • Can you post the old and new versions of your entity class? Commented Sep 26, 2014 at 8:24
  • Sure, but it didn't change. I'm just adding a unique constraint to the db. Commented Sep 26, 2014 at 8:25
  • 1
    It should not have been dropped, but the columns in the composite index are in the wrong order, I think Commented Sep 26, 2014 at 8:27
  • I'm confused. You say the entity class didn't change, but also that you added a composite index to it? Commented Sep 26, 2014 at 8:29
  • @DavidG I added the model and mapping classes for you viewing pleasure. Commented Sep 26, 2014 at 8:33

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

1 Comment

If anyone stumbles over this, somebody created a question on entity framework github: github.com/dotnet/efcore/issues/31788, and member replied "Yes, this is by-design. A composite index over two columns A and B can inherently also be used as an index over just A. However, it cannot be used as an over B."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.