Entity with compound primary key also create index on single column [duplicate]

Question

I just added an EF entity called PurchaseOrderProduct, which creates a many-to-many link between my PurchaseOrders and Products.

[PrimaryKey(nameof(PurchaseOrderId), nameof(ProductId))]
public class PurchaseOrderProduct
{
    public int PurchaseOrderId { get; set; }
    public PurchaseOrder PurchaseOrder { get; set; }

    public int ProductId { get; set; }
    public Product Product { get; set; }
}

However, I don't quite understand the generated migration script. I want the primary key to be a compound key based on the PurchaseOrderId and ProductId columns. But why does it also create an additional IX_PurchaseOrderProducts_ProductId index on my ProductId column?

/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "PurchaseOrderProducts",
        columns: table => new
        {
            PurchaseOrderId = table.Column<int>(type: "int", nullable: false),
            ProductId = table.Column<int>(type: "int", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_PurchaseOrderProducts", x => new { x.PurchaseOrderId, x.ProductId });
            table.ForeignKey(
                name: "FK_PurchaseOrderProducts_Products_ProductId",
                column: x => x.ProductId,
                principalTable: "Products",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
            table.ForeignKey(
                name: "FK_PurchaseOrderProducts_PurchaseOrders_PurchaseOrderId",
                column: x => x.PurchaseOrderId,
                principalTable: "PurchaseOrders",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);
        });

    migrationBuilder.CreateIndex(
        name: "IX_PurchaseOrderProducts_ProductId",
        table: "PurchaseOrderProducts",
        column: "ProductId");
}

Gert Arnold · Accepted Answer · 2025-10-14 17:54:54Z

3

It seems to be a consistent pattern. I can reproduce it in another many-to-many relationship, also without an explicit junction entity (like PurchaseOrderProduct). It's also shown (but not explained) in EF's own many-to-many example.

An educated guess as to why EF does it is that the composite primary key on PurchaseOrderId (as the first field) and ProductId can't be used effectively for index seeks on ProductId.

Hence a separate index on ProductId, which does allow effective query plans involving searches on ProductId only.

edited Oct 14 at 17:54

answered Oct 14 at 17:31

Gert Arnold

110k36 gold badges217 silver badges318 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jonathan Wood Oct 14 at 18:05

Yes, that's what I was thinking the most likely reason also. The compound index can be used for PurchaseOrderId. It was just a bit unexpected.

Ivan Stoev Oct 15 at 9:23

Probably Relationships - Conventions - Indexes section is a bit better reference, especially the beginning: "By convention, EF creates a database index for the property or properties of a foreign key." and the end Note: "EF does not create indexes for properties that are already covered by an existing index or primary key constraint."

Gert Arnold Oct 15 at 15:17

@Ivan Yeah, I thought of that, but at the end they say "For composite foreign keys, an index is created covering all the foreign key columns." where they only show a composite index. I'd love to find an explicit mention of this additional single-column index.

Ivan Stoev Oct 15 at 16:33

Agreed hundred percent! I also wish they restructure the documentation in way that all such things are explained in a single place, rather than jumping here and there, Notes etc.

Collectives™ on Stack Overflow

Entity with compound primary key also create index on single column [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related