.NET Core 2.2迁移生成器无法删除索引 [英] .NET Core 2.2 Migration Builder unable to remove index

问题描述

我正在尝试从我的IdentityUserRole表中名为UserRole的列中删除索引/外键.

I am trying to remove an index/foreign key from a column in my IdentityUserRole table called UserRole.

UserRole有2列.UserId和RoleId.两者都是主键.

UserRole has 2 columns. UserId and RoleId. Both are primary keys.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    var rolesTable = modelBuilder.Entity<Role>().ToTable("Role");
    var userRolesTable = modelBuilder.Entity<UserRole>().ToTable("UserRole");
    var userClaimsTable = modelBuilder.Entity<IdentityUserClaim<string>>().ToTable("UserClaim");
    var usersTable = modelBuilder.Entity<User>().ToTable("User");

    modelBuilder.Entity<Role>(builder =>
    {
        builder.Metadata.RemoveIndex(new[] { builder.Property(u => u.NormalizedName).Metadata });
    });
    modelBuilder.Entity<UserRole>(builder =>
    {
        builder.Metadata.RemoveIndex(new[] { builder.Property(u => u.RoleId).Metadata});
    });
    

}

但是，每当我运行迁移并更新数据库时，该索引仍会出现:

However, whenever i run my migration and update the database, this index still appears:

IX_UserRole_RoleId用于列RoleId

IX_UserRole_RoleId for column RoleId

这是生成的迁移

migrationBuilder.CreateTable(
    name: "UserRole",
    columns: table => new
    {
        UserId = table.Column<string>(nullable: false),
        RoleId = table.Column<string>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_UserRole", x => new { x.UserId, x.RoleId });
        table.ForeignKey(
            name: "FK_UserRole_Role_RoleId",
            column: x => x.RoleId,
            principalTable: "Role",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
        table.ForeignKey(
            name: "FK_UserRole_User_UserId",
            column: x => x.UserId,
            principalTable: "User",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

migrationBuilder.CreateIndex(
    name: "IX_UserRole_RoleId",
    table: "UserRole",
    column: "RoleId");

推荐答案

按照惯例，EF Core为每个FK列添加索引，该索引不是另一个索引的开头部分.即使您没有使用这些索引的查询，通常它们也对执行FK约束以及实现级联删除很有用，因此事实上它们在关系数据库设计中是标准的(类似于用于强制执行unique/PK约束等.)但这不是这里的问题.

By convention EF Core adds index for each FK column which is not the leading part of another index. Even though you don't have queries which use these indexes, in general they are useful for enforcing the FK constraint as well for implementing cascade delete, so de facto they are standard in relational database design (similar to unique indexes used to enforce unique/PK constraints etc.) But that's not the question here.

您可能会认为它是缺陷，但是即使您使用显式的 RemoveIndex 元数据API，约定也无法删除按惯例引入的索引.它会删除它们，但随后它们会自动添加回去.

You may consider it as defect, but indexes introduced by convention cannot be removed even though you use the explicit RemoveIndex metadata API. It removes them, but then they are automatically added back.

这是因为EF Core约定不是静态应用的.它们由监听不同的模型元数据改变的类实现，这些改变触发重新应用".惯例.

This is because the EF Core conventions are no applied statically. They are implemented by classes which listen to different model metadata changes which trigger "reapplying" the convention.

很快，您将无法使用元数据/流利的API删除此类索引.如果您确实要删除它们(我不建议这样做)，则应该手动编辑生成的迁移，并从 Up 和 Down 方法中删除相应的创建/删除命令.

Shortly, you can't remove such indexes with metadata / fluent API. If you really want to remove them (which I don't recommend), you should manually edit the generated migrations and remove corresponding create / drop commands from both Up and Down methods.

这篇关于.NET Core 2.2迁移生成器无法删除索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！