.NET Core 2.2 Migration Builder unable to remove index

user_4907861 0
Viewed 389

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

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 for column RoleId

EDIT: This is the generated migration

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");
entity-framework-core asp.net-core .net-core entity-framework c#
1 Answers

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.

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.

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.

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.

user_52025630
Related