添加Compsite索引时，EF迁移会删除索引 [英] EF Migrations drops index when adding compsite index

问题描述

我注意到当我添加带有外键的复合索引时，EF删除了外键上的索引.所以我需要更好地理解复合索引:)

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 :)

我使用此答案并生成了我的EF代码第一个迁移文件.

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

添加综合索引:

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

迁移文件:

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?

型号:

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

映射:

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

推荐答案

我得出的结论是，它是EF中的错误.

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.

这篇关于添加Compsite索引时，EF迁移会删除索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！