Why should you care?

ShopNest filters Products by CategoryId and IsPublished on every browse — composite index turns scan into seek.

See it live — copy this example

Paste into a .NET project with EF Core packages, then run with LocalDB/SQL Server (dotnet ef / dotnet run).

modelBuilder.Entity<Product>(e =>
{
    e.HasIndex(p => new { p.CategoryId, p.IsPublished, p.Name });
    e.HasIndex(p => p.Sku).IsUnique();
});

// Query uses index when filtering matching columns:
await _context.Products
    .Where(p => p.CategoryId == id && p.IsPublished)
    .OrderBy(p => p.Name)
    .ToListAsync();

What happened?

• Composite index on CategoryId, IsPublished, Name supports filter and sort.
• EF migrations emit CREATE INDEX; SQL Server optimizer uses it automatically.

Practice next

1. Log slow queries and note WHERE/ORDER BY columns.
2. Add HasIndex in Fluent API and migrate.
3. Verify index usage in SSMS actual execution plan.
4. Add filtered index WHERE IsPublished = 1.
5. Drop unused index after monitoring sys.dm_db_index_usage_stats.

Remember

Indexes match EF query patterns. Configure via Fluent HasIndex. Validate with execution plans.

ShopNest browse index

DBA adds composite index after EF logs show table scan on category browse.

Outcome: CPU on SQL node drops 30% during weekend sale.