EF Core migrations drops first foreign key index from a new composite index

Question

Here are my models:

public class Period
{
    [Key]
    public int Id { get; set; }
}

public class Company
{
    [Key]
    public int Id { get; set; }
}

public class Invoice
{
    [Key]
    public int Id { get; set; }

    public int PeriodId { get; set; }
    public Period Period { get; set; }

    public int CompanyId { get; set; }
    public Company Company { get; set; }
}

Nothing fancy. A simple invoice which has a period and a company.

Here is how they are configured:

modelBuilder.Entity<Invoice>()
    .HasOne(p => p.Period)
    .WithMany()
    .OnDelete(DeleteBehavior.Restrict)
;

modelBuilder.Entity<Invoice>()
    .HasOne(p => p.Company)
    .WithMany()
    .OnDelete(DeleteBehavior.Restrict)
;

And this is a migration file generated from this:

migrationBuilder.CreateTable(
    name: "Companies",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:Identity", "1, 1")
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Companies", x => x.Id);
    });

migrationBuilder.CreateTable(
    name: "Periods",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:Identity", "1, 1")
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Periods", x => x.Id);
    });

migrationBuilder.CreateTable(
    name: "Invoices",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:Identity", "1, 1"),
        PeriodId = table.Column<int>(nullable: false),
        CompanyId = table.Column<int>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Invoices", x => x.Id);
        table.ForeignKey(
            name: "FK_Invoices_Companies_CompanyId",
            column: x => x.CompanyId,
            principalTable: "Companies",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);
        table.ForeignKey(
            name: "FK_Invoices_Periods_PeriodId",
            column: x => x.PeriodId,
            principalTable: "Periods",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);
    });

migrationBuilder.CreateIndex(
    name: "IX_Invoices_CompanyId",
    table: "Invoices",
    column: "CompanyId");

migrationBuilder.CreateIndex(
    name: "IX_Invoices_PeriodId",
    table: "Invoices",
    column: "PeriodId");

Everything seems legit. Now I need to create a unique index which controls the uniqueness of an invoice from a company in a period:

modelBuilder.Entity<Invoice>()
    .HasIndex(p => new
    {
        p.PeriodId,
        p.CompanyId,
    })
    .IsUnique()
;

And this is how migration looks now:

migrationBuilder.DropIndex(
    name: "IX_Invoices_PeriodId",
    table: "Invoices");

migrationBuilder.CreateIndex(
    name: "IX_Invoices_PeriodId_CompanyId",
    table: "Invoices",
    columns: new[] { "PeriodId", "CompanyId" },
    unique: true);

Notice the "DropIndex". It takes the first column from my composite index. If I change the order it still drops the first one:

modelBuilder.Entity<Invoice>()
    .HasIndex(p => new
    {
        p.CompanyId,
        p.PeriodId,
    })
    .IsUnique()
;

migrationBuilder.DropIndex(
    name: "IX_Invoices_CompanyId",
    table: "Invoices");

migrationBuilder.CreateIndex(
    name: "IX_Invoices_CompanyId_PeriodId",
    table: "Invoices",
    columns: new[] { "CompanyId", "PeriodId" },
    unique: true);

I'm not sure I understand the logic behind this. Is there anything special in composite indexes that makes a separate index useless? Or perhaps it's a bug of some sort?

The only mention of this issue that I found is a bit outdated post on SO: EF Migrations drops index when adding compsite index

P.S. Tested on 3.1.8 using SQL Server as a DB provider.

score 3 · Accepted Answer · answered Sep 15 '20 at 08:33

3

Should have studied the question more thoroughly. When creating a composite index it's first column can be used separately in queries. https://github.com/dotnet/efcore/issues/22513

answered Sep 15 '20 at 08:33

Kasbolat Kumakhov

607
1
11
30

EF Core migrations drops first foreign key index from a new composite index

1 Answers1