Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 41 ยท DbContext and Models

Indexes, Constraints and Default Values

Configure indexes, unique constraints, check constraints, default values and computed columns in EF Core with the fluent API, and learn why the database should enforce your rules.

A model that only describes tables and columns is half a design. The other half is the rules the database enforces on its own: an email must be unique, a price cannot be negative, a new row gets a default status, and a lookup by name must be fast. EF Core lets you declare all of these in the model so that migrations create them for you.

The real EF Core cannot run on this site, so configuration code is read-only, and the runnable parts are C# stand-ins showing what an index and a constraint actually do.

Indexes

An index is an extra, sorted structure that lets the database find rows without scanning the whole table. EF Core creates one automatically for every foreign key. For anything else you declare it:

C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Sku);                       // plain index

    modelBuilder.Entity<Customer>()
        .HasIndex(c => c.Email)
        .IsUnique();                                 // unique index

    modelBuilder.Entity<Order>()
        .HasIndex(o => new { o.CustomerId, o.PlacedAt });   // composite index
}

The attribute form puts the index on the class:

C#
[Index(nameof(Email), IsUnique = true)]
public class Customer
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
}

Which indexes to add

  • Columns you filter on often (WHERE Email = ...).
  • Columns you sort or join on.
  • In a composite index, put the column you filter by equality first: an index on (CustomerId, PlacedAt) serves "orders of customer 7" and "orders of customer 7 in March", but not "orders in March" alone.

Every index makes reads faster and writes slower and uses disk, so index what you query, not everything.

Filtered and covering indexes

Some providers support extras through fluent methods:

C#
modelBuilder.Entity<Customer>()
    .HasIndex(c => c.Email)
    .IsUnique()
    .HasFilter("[DeletedAt] IS NULL");        // SQL Server / SQLite: unique only among active rows

modelBuilder.Entity<Order>()
    .HasIndex(o => o.PlacedAt)
    .IncludeProperties(o => o.Total);          // SQL Server: covering index

Unique constraints and alternate keys

A unique index prevents duplicates. If other tables should be able to reference the column as a foreign-key target, declare an alternate key:

C#
modelBuilder.Entity<Country>().HasAlternateKey(c => c.IsoCode);

Always keep the unique rule in the database even if you also validate in code. Two requests can both pass a "does this email exist?" check at the same moment, and only the database constraint can stop the second insert.

Check constraints

C#
modelBuilder.Entity<Product>()
    .ToTable(t => t.HasCheckConstraint("CK_Product_Price", "Price >= 0"));

The database now refuses a negative price regardless of which application writes it. In EF Core 8 and later the lambda on ToTable is the supported place for check constraints.

Default values

C#
modelBuilder.Entity<Order>()
    .Property(o => o.Status)
    .HasDefaultValue("Pending");                 // a constant

modelBuilder.Entity<Order>()
    .Property(o => o.PlacedAt)
    .HasDefaultValueSql("CURRENT_TIMESTAMP");    // computed by the database

When the property still has its CLR default (null, 0, default(DateTime)) on insert, EF Core leaves it out of the INSERT and the database fills it in; EF Core reads the value back. A classic trap: with HasDefaultValue(true) on a bool, setting the property to false looks like "not set", so the default of true wins. Use a nullable bool? or avoid a default there.

Computed columns

C#
modelBuilder.Entity<OrderLine>()
    .Property(l => l.LineTotal)
    .HasComputedColumnSql("[Quantity] * [UnitPrice]", stored: true);

The database calculates the value, EF Core never writes it, and reads it back after an insert or update. Use it for derived data you want to filter and index on.

Let the database enforce rules

Index Faster lookups
Unique No duplicates
Check Valid values only
Default Filled when missing

Model it once, migrate it

1 / 2

Stand-in: why an index helps

This stand-in is not EF Core. It compares a full scan with a dictionary lookup (the idea of an index) and shows a unique index rejecting a duplicate.

C# Stand-in: scan versus index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The index costs memory and must be updated on every insert, which is the price you pay for the fast read.

Stand-in: default and check constraints

C# Stand-in: defaults and a check constraint

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which fluent call creates a unique index on Email?

    HasIndex declares the index and IsUnique makes it reject duplicates.
  2. 2. Why keep a unique constraint in the database even if you check for duplicates in code?

    Check-then-insert has a race window. A constraint is atomic and enforced for every application.
  3. 3. For an index on (CustomerId, PlacedAt), which query benefits most?

    A composite index serves queries that use its leading column(s); the equality column should come first.
  4. 4. What is the pitfall of HasDefaultValue(true) on a bool property?

    false is the CLR default for bool, so EF Core omits it from the INSERT and the database default wins.

Summary

  • Declare indexes with HasIndex (or [Index]); foreign keys are indexed automatically.
  • IsUnique() and alternate keys enforce uniqueness in the database, where races cannot break it.
  • Check constraints, defaults and computed columns move rules and derived values into the schema.
  • Indexes speed up reads and slow down writes: index what you query.

Next: Migrations: the Basics