Detecting N+1 Problems

In this lesson, you will learn how to identify the "N+1" performance trap, where your application accidentally executes an excessive number of database queries by loading related data one row at a time.

What is the N+1 Problem?

The N+1 problem occurs when you fetch a list of parent entities (the "1" query) and then, while iterating through them, trigger an additional query for every single item to load its children (the "N" queries).

Imagine you are fetching 50 Blog entities and, for each one, you access blog.Posts. If EF Core is configured with lazy loading, or if you wrote code that manually triggers a load inside a foreach loop, EF Core will execute one query to get the blogs, plus 50 additional queries to get the posts for each blog. This turns a single database round-trip into 51, leading to massive latency.

Detecting the Issue with Logging

To spot this, you don't need a guessing game; you need to see the SQL being generated. You can configure your DbContext to log exactly what is happening in the background:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information); // Watch the console!
}

When you run your code, watch your console output. If you see a repetitive pattern of SELECT statements for every single record, you have confirmed an N+1 issue.

Using Query Tagging for Clarity

Since we’ve already covered Query Tagging, use it to label your suspicious queries so they stand out in your logs:

var blogs = context.Blogs
    .TagWith("FetchingBlogsForReport")
    .ToList();

foreach (var blog in blogs)
{
    // This access triggers the N queries!
    var posts = blog.Posts.ToList(); 
}

By tagging the initial query, you can easily filter your logs. If you see one query tagged FetchingBlogsForReport followed by dozens of identical SELECT statements for Posts, you know exactly where to apply an Include statement or a join to consolidate them into a single database hit.

Key Takeaway: The N+1 problem is invisible in C# code but glaring in your logs; always use LogTo to monitor the actual number of database round-trips generated by your loops.