Relationships and Loading

One-to-many and many-to-many navigations, Include for loading them, and the N+1 query problem with its usual fix.

Syntaxawait context.Blogs.Include(b => b.Posts).ThenInclude(p => p.Comments).ToListAsync();

Rows refer to other rows through a foreign key: a column holding another table's key. In C# you also get a navigation property, which is a property that leads to the related object or objects.

Shapes

One-to-many is a List<Post> Posts on Blog, plus int BlogId and Blog? Blog on Post. The name BlogId is what convention looks for, so nothing else is needed. Many-to-many is a list on each side, for example Post.Tags and Tag.Posts. Since EF Core 5 that is enough: EF Core creates and maintains the join table itself, named after the two entities.

Loading is a decision

A query loads the table you asked for and nothing else. Include(b => b.Posts) adds a JOIN and fills the list. ThenInclude goes one level further. Lazy loading, where touching a navigation quietly runs a query, is off unless you install the proxies package and mark navigations virtual; leave it off.

N+1

Fetch 50 blogs, then query each blog's posts inside the loop, and you send 51 queries instead of one. That is the N+1 problem. It is invisible in C# and obvious in the SQL log. Include fixes the count but returns every column of every row. The usual fix is a projection: a Select that builds exactly the fields the caller needs, so the database does the joining and sends one small result set.

Example

// dotnet add package Microsoft.EntityFrameworkCore.Sqlite
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();

using var context = new BlogContext(
    new DbContextOptionsBuilder<BlogContext>().UseSqlite(connection).Options);
await context.Database.EnsureCreatedAsync();

var blog = new Blog { Name = "Ops diary" };
blog.Posts.Add(new Post { Title = "Rollout" });
blog.Posts.Add(new Post { Title = "Rollback" });
context.Blogs.Add(blog);                 // saving the blog saves its posts too
await context.SaveChangesAsync();
context.ChangeTracker.Clear();

// One SELECT with a LEFT JOIN, because of the Include.
List<Blog> blogs = await context.Blogs.Include(b => b.Posts).ToListAsync();

foreach (Blog loaded in blogs)
{
    Console.WriteLine($"{loaded.Name} has {loaded.Posts.Count} posts");
    foreach (Post post in loaded.Posts.OrderBy(p => p.Title))
    {
        Console.WriteLine($"  {post.Title}");
    }
}

public class Blog
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Post> Posts { get; } = new();      // collection navigation
}

public class Post
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public int BlogId { get; set; }                // foreign key, found by convention
    public Blog? Blog { get; set; }                // the other end of the same relationship
}

public class BlogContext(DbContextOptions<BlogContext> options) : DbContext(options)
{
    public DbSet<Blog> Blogs => Set<Blog>();
    public DbSet<Post> Posts => Set<Post>();
}

// Output:
// Ops diary has 2 posts
//   Rollback
//   Rollout

When to use it

Return a blog together with its posts from one endpoint in a single round trip.
Replace a loop of per-row queries with one projected query when a list page is slow.
Tag posts without writing or maintaining a join entity by hand.

More examples

N+1 against the projection that replaces it

Reach for a projection first and Include only when the caller genuinely needs whole related entities. The N+1 version is the single most common performance bug in EF Core code, and it only shows itself under real row counts.

using Microsoft.EntityFrameworkCore;

// Slow: 1 query for the blogs, then 1 more for every blog. 50 blogs, 51 queries.
List<Blog> blogs = await context.Blogs.AsNoTracking().ToListAsync();
foreach (Blog blog in blogs)
{
    int count = await context.Posts.CountAsync(p => p.BlogId == blog.Id);
    Console.WriteLine($"{blog.Name}: {count}");
}

// Better: one query, and only the two values actually printed come back.
// The Count is translated into SQL and computed by the database.
var summaries = await context.Blogs
    .AsNoTracking()
    .Select(b => new { b.Name, PostCount = b.Posts.Count })
    .ToListAsync();

foreach (var summary in summaries)
{
    Console.WriteLine($"{summary.Name}: {summary.PostCount}");
}

// Include would also give one query, but it drags back every column of every
// post just to count them. Project when you only need a few fields.

Many-to-many, with the join table left to EF Core

Use this whenever the link between two tables carries no data of its own. The moment the link needs its own columns, declare a real entity for the join table and two one-to-many relationships instead.

using Microsoft.EntityFrameworkCore;

public class Post
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public List<Tag> Tags { get; } = new();
}

public class Tag
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Post> Posts { get; } = new();
}

// A list on each side is enough from EF Core 5 onwards. EF Core creates a join
// table named after the two entities (PostTag) with the two key columns in it,
// and no entity class of your own. Add the fluent configuration only when you
// need to name that table, or to put extra columns such as AddedOn in it.

// Reading: one query, a join through the hidden table.
List<Post> tagged = await context.Posts
    .Include(p => p.Tags)
    .Where(p => p.Tags.Any(t => t.Name == "kubernetes"))
    .ToListAsync();

// Writing: no join rows to manage by hand.
Post post = await context.Posts.Include(p => p.Tags).FirstAsync();
Tag tag = await context.Tags.SingleAsync(t => t.Name == "kubernetes");
post.Tags.Add(tag);
await context.SaveChangesAsync();

Filtered Include and split queries

Filtered Include is the right tool for "the blog plus its five newest posts". AsSplitQuery is for when a page of results is suddenly returning tens of thousands of duplicated rows, which the SQL makes obvious and the C# does not.

using Microsoft.EntityFrameworkCore;

// Filtered Include (EF Core 5 and later): load only some of the children.
// The filter must sit inside the Include, not in a later Where.
List<Blog> withRecent = await context.Blogs
    .Include(b => b.Posts.Where(p => p.Published).OrderByDescending(p => p.Id).Take(5))
    .ToListAsync();

// Two Includes at the same level mean one JOIN each, and the row count
// multiplies: 10 posts and 10 comments on one blog produce 100 rows, each
// repeating the blog's columns. That is cartesian explosion.
// AsSplitQuery sends one SELECT per collection instead.
List<Blog> split = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Comments)
    .AsSplitQuery()
    .ToListAsync();

// The trade: several round trips instead of one, and the queries are no longer
// a single snapshot, so a concurrent write can land between them.

Discussion

Be the first to comment on this lesson.

Shapes

Loading is a decision

N+1

Example

When to use it

More examples

N+1 against the projection that replaces it

Many-to-many, with the join table left to EF Core

Filtered Include and split queries

Related

Discussion