Indexes

An index is a separate, sorted structure that the database maintains alongside a table so that it can find rows without reading all of them. Looking up a surname in an alphabetical list of ten thousand names does not require reading ten thousand names.

Without a usable index the database reads the whole table, which is called a scan. With one it goes more or less straight to the rows it wants, which is called a seek. On a thousand rows you cannot tell the difference. On a million rows you can tell from across the room.

Indexes belong to the database, but you declare them in your model so that migrations create them. That keeps the schema and the code that depends on it in one place, under version control.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Supports: WHERE CustomerId = @id ORDER BY OrderDate DESC
    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.OrderDate });

    // A business rule, enforced by the database rather than by hope.
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Sku)
        .IsUnique();

    // Only the rows anything queries. Smaller index, cheaper writes.
    modelBuilder.Entity<Order>()
        .HasIndex(o => o.Status)
        .HasFilter("[Status] <> 3");

    modelBuilder.Entity<Employee>()
        .HasIndex(e => e.DepartmentId)
        .HasDatabaseName("IX_Employees_DepartmentId");
}

• HasIndex takes the columns. An anonymous object with several properties declares a composite index, and the order you write them in is significant — the next section is about why.
• IsUnique makes the index a constraint as well, so a duplicate SKU fails with a DbUpdateException. That is the behaviour you want for a value that must be unique, because the guarantee then holds regardless of which code path inserts the row.
• HasFilter writes a filtered index, using the database's own SQL for the predicate. Here status 3 is Cancelled, and nothing in the application queries cancelled orders by status.
• HasDatabaseName only changes the name. EF Core generates a sensible one, so reach for this when a naming convention exists that you have to match.
• The [Index] attribute does the same job from the entity class. Configuration in OnModelCreating keeps persistence concerns out of the entity type, which matters more as the model grows.

CREATE INDEX [IX_Orders_CustomerId_OrderDate]
    ON [Orders] ([CustomerId], [OrderDate]);

CREATE UNIQUE INDEX [IX_Products_Sku]
    ON [Products] ([Sku]);

CREATE INDEX [IX_Orders_Status]
    ON [Orders] ([Status])
    WHERE [Status] <> 3;

• Nothing exotic. An index declared in the model becomes an ordinary CREATE INDEX in the migration, and you can read it there before it runs anywhere.
• Review that file every time. A migration is where you find out that an index you assumed existed does not, or that one you did not intend is about to be created on a table with fifty million rows.