Loading related data and the N+1 problem

A navigation property such as blog.Posts is not loaded just because the blog was. EF Core gives you three ways to load it:

• Eager: db.Blogs.Include(b => b.Posts) loads it in the same query.
• Explicit: await db.Entry(blog).Collection(b => b.Posts).LoadAsync() issues a query when you ask.
• Lazy: with UseLazyLoadingProxies() (the Microsoft.EntityFrameworkCore.Proxies package) and virtual navigations, the first access to blog.Posts issues a query.

Without any of these, the collection is empty, or holds only the posts this context happens to be tracking already. That is a correctness bug rather than a performance one: the code runs and reports "no posts".

N+1

var blogs = await db.Blogs.ToListAsync();          // 1 query
foreach (var blog in blogs)
    total += blog.Posts.Count;                     // +1 query per blog, lazily

One query for the list, then one per row: N+1 round trips. Each is fast, so it passes in development with ten rows and collapses in production with ten thousand. The example is a model: a fake database that only counts the queries each approach would send.

1public class FakeDb2{3    public int Queries;4    readonly Dictionary<int, int> postCounts = new() { [1] = 3, [2] = 0, [3] = 5, [4] = 1 };5 6    public List<int> BlogIds() { Queries++; return postCounts.Keys.ToList(); }7    public int PostsFor(int blogId) { Queries++; return postCounts[blogId]; }      // lazy load8    public Dictionary<int, int> BlogsWithPosts() { Queries++; return new(postCounts); } // Include9}10 11public static class Solution12{13    public static List<string> Run()14    {15        var lazy = new FakeDb();16        var total = 0;17        foreach (var id in lazy.BlogIds()) total += lazy.PostsFor(id);18 19        var eager = new FakeDb();20        var total2 = eager.BlogsWithPosts().Values.Sum();21 22        return new List<string>23        {24            $"lazy: {total} posts in {lazy.Queries} queries",25            $"eager: {total2} posts in {eager.Queries} queries",26        };27    }28}

The fix has its own trap

Include turns the loop into one query with a JOIN. Include two sibling collections and the join multiplies: each blog row repeats for every post times every tag, because the posts and tags have no relationship to each other. This is cartesian explosion. When a query loads more than one collection navigation and you have not chosen a query splitting behaviour, EF Core logs a warning saying so. EF Core still builds each blog, post and tag only once from the repeated rows, so the cost is bandwidth and time rather than wrong results.

db.Blogs.Include(b => b.Posts).Include(b => b.Tags).AsSplitQuery();

AsSplitQuery() loads each collection in its own query instead: more round trips, far fewer rows. Split queries are not one atomic read, so data changed between them can be inconsistent unless you run them in a serializable or snapshot transaction. The arithmetic below counts the rows each approach returns for a hundred blogs.

1public static class Solution2{3    public static List<string> Run()4    {5        // 100 blogs, each with 20 posts and 10 tags.6        int blogs = 100, posts = 20, tags = 10;7 8        // LEFT JOIN Posts LEFT JOIN Tags: every post pairs with every tag of its blog.9        var singleRows = blogs * posts * tags;10 11        // One query per collection.12        var splitRows = blogs + blogs * posts + blogs * tags;13 14        return new List<string>15        {16            $"single query: 1 round trip, {singleRows} rows",17            $"split query: 3 round trips, {splitRows} rows",18        };19    }20}

Often the best fix is neither: project only what the page needs.

var rows = await db.Blogs
    .Select(b => new { b.Title, PostCount = b.Posts.Count })
    .ToListAsync();                                // one query, two columns

Common mistakes

• Enabling lazy loading globally and serialising entities to JSON. The serialiser walks every navigation and triggers a query for each one.
• Fixing N+1 by including every collection. Two sibling collection includes on a large parent set can return far more rows than the N+1 version did.
• Loading whole entities to display two fields. A projection is one narrow query, and its result is not tracked.
• Not looking at the SQL. Turn on Microsoft.EntityFrameworkCore.Database.Command logging, or LogTo, and count the statements per request.

What interviewers ask

• What is the N+1 problem and how do you detect it? One query for a list plus one per item via lazy navigation; spot it in SQL logs or an APM trace showing many identical statements per request.
• Include or AsSplitQuery? Single query for one collection or small sets; split for several collections where the join multiplies; mention the consistency trade-off.
• When would you avoid loading entities at all? Read-only views: project with Select to a DTO.