Relationships and efficient queries

Model one-to-many and many-to-many relationships, load related data correctly, and avoid N+1 queries.

Relationships in the model

Relationships are expressed with navigation properties and (usually) a foreign key property:

public class Customer
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Order> Orders { get; set; } = [];        // one customer → many orders
}
 
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }                  // foreign key
    public Customer Customer { get; set; } = null!;      // navigation to the "one" side
    public List<OrderLine> Lines { get; set; } = [];
    public DateTime PlacedAt { get; set; }
}
 
public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; } = null!;
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
}

EF Core infers one-to-many relationships from these conventions. Many-to-many works with collections on both sides — EF Core creates the join table:

public class Post { public int Id { get; set; } public List<Tag> Tags { get; set; } = []; }
public class Tag  { public int Id { get; set; } public List<Post> Posts { get; set; } = []; }

Configure delete behavior, required relationships and keys explicitly with the fluent API when conventions aren't enough.

By default, a query loads only the entity you asked for — order.Customer is null unless you load it. Options:

// 1. Eager loading with Include — a JOIN in the same query
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines).ThenInclude(l => l.Product)
    .Where(o => o.PlacedAt >= since)
    .ToListAsync();
 
// 2. Projection — fetch exactly what you need (often the best choice for APIs)
var summaries = await db.Orders
    .Where(o => o.PlacedAt >= since)
    .Select(o => new OrderSummary(
        o.Id,
        o.Customer.Name,                          // navigation used inside the query → JOIN
        o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
    .ToListAsync();

Compare the queries — and the N+1 problem — in the viewer (the Include (JOIN) and N+1 problem tabs):

Interactive

LINQ → SQL

What Entity Framework Core actually sends to the database (SQL Server)

C# (LINQ)

var cheap = await db.Products
    .Where(p => p.Price < 20)
    .OrderBy(p => p.Name)
    .ToListAsync();

Generated SQL1 query sent

SELECT [p].[Id], [p].[Name], [p].[Price], [p].[Stock]
FROM [Products] AS [p]
WHERE [p].[Price] < 20.0
ORDER BY [p].[Name]

Rows returned

Id	Name	Price	Stock
2	Mouse	19.99	10
3	Notebook	4.5	100
4	Pen	1.2	0

Where becomes WHERE and OrderBy becomes ORDER BY. The filtering happens in the database — only matching rows travel over the network.

The N+1 problem

With lazy loading enabled (an opt-in feature), touching a navigation property triggers a query on the spot. Inside a loop, that's one query per row:

foreach (var order in await db.Orders.ToListAsync())       // 1 query
    Console.WriteLine(order.Customer.Name);                 // + N queries

With 10,000 orders, that's 10,001 round trips. Fix it with Include or a projection — and watch the SQL log during development.

Cartesian explosion

Including several collections at once (Include(o => o.Lines).Include(o => o.Payments)) can multiply rows in a single JOIN. For such queries, .AsSplitQuery() tells EF Core to load each collection with a separate query.

Tracking vs no-tracking

Tracking costs memory and CPU — EF Core keeps a snapshot of every entity to detect changes. For read-only queries (most GET endpoints), turn it off:

var products = await db.Products.AsNoTracking().Where(p => p.Stock > 0).ToListAsync();

Projections into DTOs are never tracked.

var order = new Order
{
    CustomerId = 7,
    PlacedAt = DateTime.UtcNow,
    Lines = [new() { ProductId = 1, Quantity = 2, UnitPrice = 19.99m }],
};
db.Orders.Add(order);           // the lines are inserted too
await db.SaveChangesAsync();    // order.Id and the lines' OrderId are filled in

Concurrency

Two users edit the same product at once — who wins? Add a concurrency token and EF Core checks it on update:

[Timestamp] public byte[] RowVersion { get; set; } = [];
// SaveChanges throws DbUpdateConcurrencyException if someone else changed the row first

Key takeaways

Navigation properties + foreign keys define one-to-many and many-to-many relationships.
Related data isn't loaded automatically: use Include/ThenInclude, or better, Select projections.
Lazy loading inside loops causes N+1 queries — load what you need up front.
Use AsNoTracking() for read-only queries and concurrency tokens for conflicting edits.

Relationships and efficient queries

You will learn

Relationships in the model

The N+1 problem

Tracking vs no-tracking

Concurrency

Key takeaways

Quiz

Relationships and efficient queries

You will learn

Relationships in the model

Loading related data

The N+1 problem

Tracking vs no-tracking

Saving related data

Concurrency

Key takeaways

Quiz