Relationships

Model one-to-many and many-to-many relationships, choose delete behaviours, and load related data with Include or projections.

Syntax

modelBuilder.Entity<Order>()
    .HasOne(o => o.Customer)
    .WithMany(c => c.Orders)
    .HasForeignKey(o => o.CustomerId)
    .OnDelete(DeleteBehavior.Restrict);

A relationship is a foreign key plus optional navigations: a reference navigation (order.Customer) on the dependent and a collection navigation (customer.Orders) on the principal. With a property called CustomerId next to Customer, EF finds the relationship by convention, creates the foreign key constraint and adds an index on CustomerId. Navigations are a C# convenience; the database only ever sees the key column.

Required, optional and delete behaviour

The nullability of the foreign key decides whether the relationship is required: int CustomerId is required, int? ApprovedById is optional. That also picks the default delete behaviour.

Relationship	Default	Database constraint	EF on tracked dependents
Required FK	`Cascade`	`ON DELETE CASCADE`	Marks them `Deleted`
Optional FK	`ClientSetNull`	No action	Sets the FK to null
Explicit `Restrict`		No action / restrict	Throws if dependents are loaded

Cascade is right for parts that cannot exist alone (order lines). It is dangerous for business records: deleting a customer that cascades to orders, invoices and payments destroys financial history in one statement. Use OnDelete(DeleteBehavior.Restrict) there and soft-delete or anonymise the customer instead. SQL Server additionally refuses two cascade paths to the same table (may cause cycles or multiple cascade paths), which you resolve by making one of them Restrict or NoAction.

Removing a dependent from a collection of a required relationship deletes it: order.Lines.Remove(line) followed by SaveChangesAsync sends a DELETE, because a line cannot exist without an order. For an optional relationship the same call sets the foreign key to null instead.

Many-to-many

Two collection navigations (Product.Categories and Category.Products) are enough: EF creates a join table CategoryProduct with a composite key and cascading foreign keys, and adding to either collection inserts a join row. When the link has its own data, such as a quantity and unit price between an order and a product, model the join as a real entity (OrderLine) with two one-to-many relationships. In practice most join tables grow a column eventually, so start explicit whenever the link means something to the business.

Loading related data

Navigations are empty unless you load them. Include(o => o.Lines).ThenInclude(l => l.Product) loads eagerly in the same query; filtered includes such as Include(c => c.Orders.Where(o => o.Status == "Open")) limit what comes back. Entry(order).Collection(o => o.Lines).LoadAsync() loads explicitly later. Lazy loading through proxies is off by default; leave it off in APIs, because each navigation access in a loop becomes a query (N+1). For read endpoints a Select into a DTO beats Include: it fetches only the needed columns and never serialises an entity graph, where Order.Customer.Orders back-references make System.Text.Json throw A possible object cycle was detected.

When an entity has two navigations to the same type (Customer and ApprovedBy) and that type has an inverse collection (Customer.Refunds), conventions cannot tell which reference it pairs with, and model building fails with Unable to determine the relationship represented by navigation; configure each pair with HasOne(...).WithMany(...).

Example

using Microsoft.EntityFrameworkCore;

public sealed class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // One-to-many, required. Restrict: deleting a customer with orders must fail, not cascade.
        modelBuilder.Entity<Order>()
            .HasOne(o => o.Customer)
            .WithMany(c => c.Orders)
            .HasForeignKey(o => o.CustomerId)
            .OnDelete(DeleteBehavior.Restrict);

        // Order lines are part of the order: cascade, and removing one from the collection deletes it.
        modelBuilder.Entity<OrderLine>()
            .HasOne(l => l.Order)
            .WithMany(o => o.Lines)
            .HasForeignKey(l => l.OrderId)
            .OnDelete(DeleteBehavior.Cascade);

        // A line points at a product, but deleting a product must not delete sales history.
        modelBuilder.Entity<OrderLine>()
            .HasOne(l => l.Product)
            .WithMany()
            .HasForeignKey(l => l.ProductId)
            .OnDelete(DeleteBehavior.Restrict);

        // Many-to-many with no payload: EF creates the CategoryProduct join table.
        modelBuilder.Entity<Product>()
            .HasMany(p => p.Categories)
            .WithMany(c => c.Products);
    }
}

public static class OrderQueries
{
    // Command side: load the aggregate to modify it.
    public static Task<Order?> LoadForEditAsync(ShopDb db, int orderId, CancellationToken ct) =>
        db.Orders
            .Include(o => o.Lines)
                .ThenInclude(l => l.Product)
            .SingleOrDefaultAsync(o => o.Id == orderId, ct);

    // Read side: project; no Include, no tracking, no cycles to serialise.
    public static Task<List<CustomerOrders>> OpenOrdersAsync(ShopDb db, CancellationToken ct) =>
        db.Customers
            .Where(c => c.Orders.Any(o => o.Status == "Open"))
            .Select(c => new CustomerOrders(
                c.Name,
                c.Orders.Where(o => o.Status == "Open")
                    .Select(o => new OrderTotal(o.Id, o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
                    .ToList()))
            .ToListAsync(ct);
}

public sealed record CustomerOrders(string Customer, List<OrderTotal> OpenOrders);
public sealed record OrderTotal(int OrderId, decimal Total);

public sealed class Customer
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Order> Orders { get; } = [];
}

public sealed class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public required string Status { get; set; }
    public List<OrderLine> Lines { get; } = [];
}

public sealed class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public Order Order { get; set; } = null!;
    public int ProductId { get; set; }
    public Product Product { get; set; } = null!;
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }      // price at the time of sale, not Product.Price
}

public sealed class Product
{
    public int Id { get; set; }
    public required string Sku { get; set; }
    public decimal Price { get; set; }
    public List<Category> Categories { get; } = [];
}

public sealed class Category
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Product> Products { get; } = [];
}

When to use it

Make DELETE /customers/{id} fail with 409 when the customer has orders instead of cascading into invoices and payments
Tag products with categories through a many-to-many with no join entity, then add a join entity when sort order is needed
Return a customer with open order totals through a Select projection instead of Include and a JSON object cycle

More examples

Two navigations to the same type

With two references from <code>Refund</code> to <code>Customer</code> and one collection back, EF cannot know which reference pairs with <code>Customer.Refunds</code>. Configuring both relationships removes the guesswork. <code>WithMany()</code> with no argument declares a relationship that has no inverse navigation, which is common for audit links such as approved-by.

using Microsoft.EntityFrameworkCore;

// Without OnModelCreating this model fails to build:
// "Unable to determine the relationship represented by navigation 'Customer.Refunds'".
public sealed class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public DbSet<Refund> Refunds => Set<Refund>();
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Refund>(refund =>
        {
            refund.HasOne(r => r.Customer)
                .WithMany(c => c.Refunds)
                .HasForeignKey(r => r.CustomerId)
                .OnDelete(DeleteBehavior.Restrict);

            // Optional, no inverse navigation; ClientSetNull by default.
            refund.HasOne(r => r.ApprovedBy)
                .WithMany()
                .HasForeignKey(r => r.ApprovedById);
        });
    }
}

public sealed class Customer
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Refund> Refunds { get; } = [];
}

public sealed class Refund
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public int? ApprovedById { get; set; }      // a staff member stored as a Customer row
    public Customer? ApprovedBy { get; set; }
}

Many-to-many with a payload via an explicit join entity

<code>UsingEntity<StockLevel></code> keeps the convenient skip navigations (<code>product.Warehouses</code>) and makes the join row a real entity with <code>OnHand</code> and <code>Reserved</code>. Queries that need stock numbers go through <code>StockLevels</code>; queries that only need where a product is stocked use <code>Warehouses</code>. Moving from an implicit join table to this later needs a migration that renames the table and adds columns.

using Microsoft.EntityFrameworkCore;

public sealed class CatalogDb(DbContextOptions<CatalogDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Warehouse> Warehouses => Set<Warehouse>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Skip navigations stay available (product.Warehouses) while the join row carries stock data.
        modelBuilder.Entity<Product>()
            .HasMany(p => p.Warehouses)
            .WithMany(w => w.Products)
            .UsingEntity<StockLevel>(
                right => right.HasOne(s => s.Warehouse).WithMany().HasForeignKey(s => s.WarehouseId),
                left => left.HasOne(s => s.Product).WithMany(p => p.StockLevels).HasForeignKey(s => s.ProductId),
                join =>
                {
                    join.ToTable("stock_levels");
                    join.HasKey(s => new { s.ProductId, s.WarehouseId });
                    join.Property(s => s.UpdatedAt).HasDefaultValueSql("now()");
                });
    }
}

public sealed class Product
{
    public int Id { get; set; }
    public required string Sku { get; set; }
    public List<Warehouse> Warehouses { get; } = [];
    public List<StockLevel> StockLevels { get; } = [];
}

public sealed class Warehouse
{
    public int Id { get; set; }
    public required string Code { get; set; }   // e.g. FRA-1
    public List<Product> Products { get; } = [];
}

public sealed class StockLevel
{
    public int ProductId { get; set; }
    public Product Product { get; set; } = null!;
    public int WarehouseId { get; set; }
    public Warehouse Warehouse { get; set; } = null!;
    public int OnHand { get; set; }
    public int Reserved { get; set; }
    public DateTime UpdatedAt { get; set; }
}

The schema EF builds for these relationships

Each <code>OnDelete</code> choice is visible in the constraints. <code>OrderLine</code> has no <code>DbSet</code>, so its table takes the class name. The join table is named after both entity types in alphabetical order with columns named after the navigations plus <code>Id</code>; EF indexes <code>ProductsId</code> separately because the primary key already covers lookups by <code>CategoriesId</code>.

CREATE TABLE "Orders" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "CustomerId" integer NOT NULL,
    "Status" text NOT NULL,
    CONSTRAINT "PK_Orders" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Orders_Customers_CustomerId" FOREIGN KEY ("CustomerId")
        REFERENCES "Customers" ("Id") ON DELETE RESTRICT
);

CREATE TABLE "OrderLine" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "OrderId" integer NOT NULL,
    "ProductId" integer NOT NULL,
    "Quantity" integer NOT NULL,
    "UnitPrice" numeric NOT NULL,
    CONSTRAINT "PK_OrderLine" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_OrderLine_Orders_OrderId" FOREIGN KEY ("OrderId")
        REFERENCES "Orders" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_OrderLine_Products_ProductId" FOREIGN KEY ("ProductId")
        REFERENCES "Products" ("Id") ON DELETE RESTRICT
);

-- Implicit many-to-many join table: composite key, both sides cascade.
CREATE TABLE "CategoryProduct" (
    "CategoriesId" integer NOT NULL,
    "ProductsId" integer NOT NULL,
    CONSTRAINT "PK_CategoryProduct" PRIMARY KEY ("CategoriesId", "ProductsId"),
    CONSTRAINT "FK_CategoryProduct_Categories_CategoriesId" FOREIGN KEY ("CategoriesId")
        REFERENCES "Categories" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_CategoryProduct_Products_ProductsId" FOREIGN KEY ("ProductsId")
        REFERENCES "Products" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_Orders_CustomerId" ON "Orders" ("CustomerId");
CREATE INDEX "IX_OrderLine_OrderId" ON "OrderLine" ("OrderId");
CREATE INDEX "IX_OrderLine_ProductId" ON "OrderLine" ("ProductId");
CREATE INDEX "IX_CategoryProduct_ProductsId" ON "CategoryProduct" ("ProductsId");

Discussion

Be the first to comment on this lesson.