Configure Indexes, Uniqueness, Sort Order, Filters, Included Columns, and Provider Differences

Learning outcomes

ServiceHub can now identify work orders correctly, but correctness alone does not make lookups efficient. Dispatch screens filter by priority and time, integrations search by external reference, and operators sort recent work. Indexes are physical database structures with write/storage costs, and EF's index model is only a configuration description until a provider turns it into DDL.

1. An index is an access path, not an automatic speed switch

An index stores key values in an order/structure the database can exploit for some predicates, joins, ordering, or uniqueness checks. Every additional index also consumes storage and adds maintenance work to inserts, deletes, and updates of indexed columns. Therefore “index every filter column” is not a production strategy.

2. Add a unique index for an integration reference—not an alternate key

ServiceHub receives an optional external ticket reference from partner systems. It must be unique when present, but no ServiceHub relationship should use it as principal identity. That is a unique-index use case rather than an alternate-key use case.

public string? ExternalReference { get; private set; }public void AttachExternalReference(string reference){    ExternalReference = string.IsNullOrWhiteSpace(reference)        ? throw new ArgumentException("Reference is required.", nameof(reference))        : reference.Trim();}

builder.Property(x => x.ExternalReference)    .HasColumnName("external_reference")    .HasMaxLength(80);builder.HasIndex(x => x.ExternalReference)    .IsUnique()    .HasDatabaseName("UX_work_orders_external_reference");

SQLite permits multiple NULL values in a unique index. Other database engines have their own null/unique semantics and provider conventions. If your business rule says “exactly one row may have null” or “nulls are not allowed,” express that rule explicitly instead of assuming provider behavior.

3. Composite indexes are ordered tuples

The dispatch queue often filters by priority and then presents work-order numbers in descending sequence. For the mandatory SQLite lab, use a composite index on (Priority, WorkOrderNumber). This choice is deliberate: the current SQLite provider can read/write DateTimeOffset but does not natively translate comparison/ordering over it. SQL Server and other providers have different capabilities, so OpenedUtc-based ordering is demonstrated only in provider-specific examples.

builder.HasIndex(x => new { x.Priority, x.WorkOrderNumber })    .IsDescending(false, true)    .HasDatabaseName("IX_work_orders_priority_number");

The first column is ascending and the second descending in index metadata. Whether this exact ordering is honored/useful depends on the provider and engine. Do not quote “leftmost prefix” as a magical rule without inspecting the target database's planner and actual queries.

var queue = await db.WorkOrders    .Where(x => x.Priority == WorkOrderPriority.High)    .OrderByDescending(x => x.WorkOrderNumber)    .Take(50)    .Select(x => new { x.Id, x.WorkOrderNumber, x.CustomerName, x.OpenedUtc })    .ToListAsync(ct);

Later performance chapters will measure end-to-end behavior. Here the goal is simply to make index intent and generated SQL visible.

4. Avoid redundant indexes created by identity constraints

An alternate key already creates the relational uniqueness structure needed to enforce that key. Adding a second unique index on the exact same WorkOrderNumber column usually duplicates maintenance/storage without adding semantics.

builder.HasAlternateKey(x => x.WorkOrderNumber);// Usually redundant on the same property set:builder.HasIndex(x => x.WorkOrderNumber).IsUnique();

Inspect generated migrations and database catalogs before deciding two structures are necessary. Some engines implement constraints using indexes internally; others expose constraint/index metadata differently. The rule is not “constraints and indexes are identical,” but “do not create duplicate physical work without evidence.”

5. Provider-specific filters and included columns must be gated

SQL Server can use filtered indexes and non-key included columns. EF's SQL Server provider exposes convenient extensions for those capabilities. The mandatory course lab remains SQLite, so these snippets are explicitly provider-specific and must not be copied into a supposedly portable mapping layer without a provider boundary.

// Requires Microsoft.EntityFrameworkCore.SqlServer.// Illustrative provider-specific configuration; not used by the SQLite lab.builder.HasIndex(x => new { x.Priority, x.OpenedUtc })    .HasDatabaseName("IX_work_orders_open_queue")    .HasFilter("[closed_utc] IS NULL")    .IncludeProperties(x => new    {        x.WorkOrderNumber,        x.CustomerName    });

HasFilter contains SQL text and therefore is provider/database syntax. IncludeProperties is a SQL Server provider extension. PostgreSQL, MySQL/MariaDB, Oracle, and SQLite have different capabilities and syntax. Chapter 21 builds a formal cross-provider test matrix.

6. Inspect index metadata before touching the database

var entity = db.Model.FindEntityType(typeof(WorkOrder))!;foreach (var index in entity.GetIndexes()){    Console.WriteLine(index.GetDatabaseName());    Console.WriteLine($"  Unique: {index.IsUnique}");    Console.WriteLine($"  Properties: {string.Join(", ", index.Properties.Select(p => p.Name))}");    Console.WriteLine($"  Descending: {string.Join(", ", index.IsDescending ?? Array.Empty<bool>())}");    Console.WriteLine($"  Filter: {index.GetFilter() ?? "<none>"}");}

Runtime metadata proves what EF intends to ask the provider to create. It does not prove the database has already applied the migration, nor that the optimizer will choose the index for a query.

7. Inspect SQLite's actual index catalog

SELECT name, sqlFROM sqlite_schemaWHERE type = 'index'  AND tbl_name = 'work_orders'ORDER BY name;

For deeper inspection, SQLite exposes pragmas such as PRAGMA index_list('work_orders') and PRAGMA index_xinfo('index_name'). Those commands are database evidence, separate from EF metadata.

EXPLAIN QUERY PLANSELECT work_order_id, work_order_number, customer_name, opened_utcFROM work_ordersWHERE priority = 2ORDER BY work_order_number DESCLIMIT 50;

Do not manufacture a claim like “the query is 40% faster” from this lab. Dataset size, distributions, SQLite statistics, cache state, machine, query shape, and write load all matter. Record the plan text and explain what it shows.

8. Deliberately wrong approach: build an index portfolio from property names

A common ORM anti-pattern is to scan entity properties and create indexes for anything ending in Id, Date, or Status. That can produce a large write-amplification tax while still missing the composite query shape that actually matters.

9. Hands-on lab: create and prove a small index portfolio

1. Add optional ExternalReference and its unique index.
2. Add the (Priority, WorkOrderNumber) composite index with explicit sort order for the SQLite lab.
3. Do not add a duplicate unique index on WorkOrderNumber because it is already an alternate key.
4. Generate Chapter04Indexes and inspect every CreateIndex/AddUniqueConstraint operation.
5. Apply only to the disposable SQLite database.
6. Inspect sqlite_schema and PRAGMA index_xinfo.
7. Seed enough rows to make the query shape meaningful; record exact row count/distribution if you compare plans.
8. Run EXPLAIN QUERY PLAN for the priority/recent query before and after the composite index in separate resettable database states.
9. Keep SQL Server filter/include configuration only as a documented provider-specific variant.

Verification checklist

• Index names are stable and descriptive.
• The alternate-key property is not redundantly indexed without justification.
• SQLite catalog output matches EF migration intent.
• Any query-plan claim includes the exact query and lab conditions.
• No SQL Server-only extension exists in the mandatory SQLite path.

10. Production judgment and bridge

Indexes should be few enough to justify their write cost and specific enough to support observed workloads. Keep provider-specific index features behind explicit provider boundaries, test null/unique semantics on the real engine, and pair EF logs with database plans before making tuning claims.

Lesson 3 shifts from indexes to value ownership: which values the application supplies, which EF temporarily assigns, and which the database generates on insert/update. That distinction determines generated SQL and what values EF expects back after SaveChanges.

Authoritative references

• Indexes - EF Core — single/composite indexes, uniqueness, names, sort order, filters, and included columns
• Efficient Querying - EF Core — index-aware query design and database-plan evidence
• SQL Server Indexes - EF Core provider — SQL Server-specific index configuration
• SQLite Query Planner — SQLite planner/index background for the local lab
• Microsoft.EntityFrameworkCore.Sqlite 10.0.11 — current SQLite provider checkpoint