Avoiding the N+1 Query Problem

Analogy categoryAnalogies

How the N+1 Problem Shows Up in EF Core

The N+1 problem happens when you run one query to fetch a list of N entities, then trigger a separate query per entity to fetch related data — typically via lazy loading. For example, foreach (var blog in context.Blogs.ToList()) { var count = blog.Posts.Count(); } issues 1 query for the blogs plus N additional queries, one per blog, as each Posts navigation property is lazily accessed inside the loop. With 500 blogs that's 501 round trips to the database instead of 1 or 2, and the latency compounds because each round trip pays the full network and query-planning cost even for tiny result sets.

🏏

Cricket analogy: It's like a scorer walking to the pavilion to check each individual player's career average one at a time during a match instead of pulling the whole squad's stats sheet once before play starts.

Eager Loading with Include and ThenInclude

The most direct fix is eager loading: use .Include(b => b.Posts) so EF Core generates a single SQL query (typically a JOIN, or in newer EF Core versions a split query) that retrieves blogs and their related posts together in one round trip. For multi-level relationships, chain .ThenInclude(p => p.Comments) to pull in a third level. Eager loading turns the earlier 501-query example into exactly one query, at the cost of a potentially larger result set if the JOIN produces a wide cartesian product across multiple one-to-many Includes — which is one reason EF Core offers split queries as an alternative.

🏏

Cricket analogy: It's like requesting the full match scorecard with batting, bowling, and fielding stats bundled together in one PDF from the scorers' table, instead of separately asking for each category one at a time.

Projection and Split Queries as Alternatives

When you only need a few fields rather than full entities, projecting with .Select() into a DTO lets EF Core generate a single, narrow query that pulls exactly the columns needed — often via a correlated subquery or JOIN — without materializing entire entity graphs or triggering lazy loads at all. For cases where a single Include-based JOIN would be inefficient (say, including two separate one-to-many collections on the same root, which multiplies row counts), .AsSplitQuery() tells EF Core to issue multiple SQL queries — still just one per navigation, not one per row — using a shared filter so you avoid both the N+1 problem and the cartesian explosion of a single giant JOIN.

🏏

Cricket analogy: It's like requesting just the day's required run-rate and required balls displayed on the scoreboard instead of the entire ball-by-ball commentary feed, when that's the only information the broadcast actually needs.

csharp

// N+1 problem: one query per blog inside the loop (lazy loading)
foreach (var blog in context.Blogs.ToList())
{
    Console.WriteLine($"{blog.Url}: {blog.Posts.Count()} posts"); // triggers a query each iteration
}

// Fix 1: eager loading collapses it into a single query
var blogsWithPosts = context.Blogs
    .Include(b => b.Posts)
        .ThenInclude(p => p.Comments)
    .ToList();

// Fix 2: projection avoids loading full entity graphs entirely
var summaries = context.Blogs
    .Select(b => new BlogSummaryDto
    {
        Url = b.Url,
        PostCount = b.Posts.Count()
    })
    .ToList(); // one SQL query with a correlated subquery, no lazy loads

// Fix 3: split query avoids a cartesian explosion from two collections
var blogsSplit = context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Subscribers)
    .AsSplitQuery()
    .ToList();

Lazy loading (enabled via UseLazyLoadingProxies or manual ILazyLoaderfy injection) is the most common source of accidental N+1 queries because navigation property access looks like a harmless in-memory read but silently issues a database round trip.

The N+1 problem occurs when one query for N entities is followed by N more queries for related data, usually via lazy loading in a loop.
Include() and ThenInclude() collapse related-data fetching into a single SQL query using a JOIN (or split queries).
Projecting with Select() into a DTO avoids materializing full entity graphs and can sidestep lazy loading entirely.
AsSplitQuery() issues one query per navigation instead of one giant JOIN, avoiding cartesian-product row multiplication across multiple collections.
Lazy loading is convenient but is the most common accidental cause of N+1 queries, since navigation access looks like a free in-memory read.
Prefer explicit Include/Select over lazy loading in hot paths like list rendering or API endpoints serving many rows.
Profiling generated SQL (e.g. via logging or a tool like MiniProfiler) is the most reliable way to catch N+1 patterns before they hit production.