Efcore: Is there a way to alter or remove a previously declared index?

[scottste]

Steps to reproduce

Create a default project and add a line similar to the following in the OnModelCreating method:

builder.Entity<ApplicationUser>()
                .HasIndex(u => new { u.NormalizedUserName, u.AppTenantId })
                .HasName("UserNameIndex")
                .IsUnique();

The issue

I would expect since I'm explicitly passing the index name that any other indexes with the same name would be removed/ignored instead of just adding another index. If this behavior is intended, is there a way to change/delete an index that was previously declared? My use case is that I'm using Identity and would like to change the user name index for a multi-tenant application so that different tenants can have the same username. My current workaround is to just not call the base OnModelCreating method and instead copy everything but the one index line.

Further technical details

EF Core version: 1.0.0
Operating system: Windows 10
Visual Studio version: VS 2015

[gdoron]

Check these methods (available inside a migration)

migrationBuilder.CreateIndex()
migrationBuilder.DropIndex()
migrationBuilder.RenameIndex()

I would expect since I'm explicitly passing the index name that any other indexes with the same name would be removed/ignored instead of just adding another index.

Regarding the drop of the existing index: :scream: :scream: :scream: :scream:

Regarding multiple indices with the same name:

index_name Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database.

Spec

[divega]

Triage: @AndriySvyryd we would like your opinion on what the behavior should be.

[AndriySvyryd]

** Edit: wrong Scott 😄
@scottste Apart from changing the migration as @gdoron mentioned. You can remove the index from the model by using the Metadata API:

C# var index = builder.Entity<ApplicationUser>() .HasIndex(u => new { u.NormalizedUserName, u.AppTenantId }).Metadata var applicationUserType = builder.Entity<ApplicationUser>().Metadata.RemoveIndex(index.Properties);

This should be done after calling base.OnModelCreating() to avoid it being recreated.

[gdoron]

+1 for the ScottGu. ping.. :laughing:
@AndriySvyryd

[YehudahA]

@AndriySvyryd, Your solution doesn't work with foreign keys.

Example:

public class Parent
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ParentId { get; set; }

    public virtual Parent Parent { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Parent> Parents { get; set; }
    public DbSet<Child> Children { get; set; }


    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.\sqlexpress;Database=MyDb;Trusted_Connection=True");
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        var entityMetadata = modelBuilder.Entity<Child>().Metadata;

        var indexes = entityMetadata.GetIndexes().ToList();

        foreach (var index in indexes)
        {
            entityMetadata.RemoveIndex(index);
        }
    }
}

Created migratoin:

public partial class Initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Parents",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Parents", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "Children",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(nullable: true),
                ParentId = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Children", x => x.Id);
                table.ForeignKey(
                    name: "FK_Children_Parents_ParentId",
                    column: x => x.ParentId,
                    principalTable: "Parents",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Children_ParentId",
            table: "Children",
            column: "ParentId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Children");

        migrationBuilder.DropTable(
            name: "Parents");
    }
}

[AndriySvyryd]

@YehudahA The proper way of doing this is tracked by https://github.com/dotnet/efcore/issues/7605. #214 could also mitigate it by removing the convention that adds it.

The best workaround currently is to remove the offending line from the migration.