How to Find and Fix N+1 Queries in EF Core

The API was screaming. A single endpoint, perfectly fine on my machine, was bringing our database to its knees in production. After digging through the logs, I found the culprit: one request was generating 241 separate SQL queries.

This one burned me badly early in my career. It’s a classic trap that every developer using an ORM like Entity Framework Core will eventually fall into: the N+1 query problem. It’s silent in development but absolutely lethal at scale.

So, What’s an N+1 Query Anyway?

It’s a sneaky performance bug. You ask EF Core for a list of items, and it happily obliges with one query. That’s the “1”.

Then, you loop through that list, and for each item, you access a related property (like a post’s author). If you haven’t told EF Core to load that related data upfront, it goes back to the database for every single item. That’s the “N”.

So, to get 100 blog posts and their authors, you end up with:

• 1 query to get the 100 posts.
• 100 more queries to get each post’s author.
• Total: 101 queries for what should have been a simple operation.

In that production disaster I mentioned, fixing the N+1 pattern dropped the query count from 241 to just 3. The response time fell by over 85%.

How to Spot an N+1 Ambush

You can’t fix what you can’t see. Here are my go-to methods for hunting down these hidden performance killers.

1. Just Watch the Logs

This is the most direct way to see the problem. Turn on EF Core’s logging in your Program.cs during development.

builder.Services.AddDbContext<BlogContext>(options =>
    options.UseSqlServer(connectionString)
           // This is noisy, but great for debugging
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging()); // Only in dev!

Now run your app and hit the endpoint. If you see a flood of identical SELECT statements with different WHERE clauses, you’ve found an N+1.

2. Use a Real Database Profiler

Console logging can get noisy. For a clearer picture, especially under load, use a proper tool. SQL Server Profiler or the profiler in Azure Data Studio will show you every single query hitting the database in real-time. It’s perfect for spotting repeated query patterns that signal an N+1.

3. Write a Test That Fails Loudly

This is my favorite defense. Create an integration test that actually counts the queries. This catches regressions before they ever get merged. You’ll need a way to count queries, which you can do with a custom DbCommandInterceptor.

Once you have a counter, your test can be simple:

[Test]
public async Task GetPostsWithAuthors_ShouldExecuteInThreeQueriesOrLess()
{
    // Arrange: reset your query counter
    _queryCounter.Reset();
    
    // Act
    await _blogService.GetPostsWithAuthors();
    
    // Assert
    // A good number might be 1, 2, or 3, depending on the strategy.
    // Anything over a handful is suspicious.
    Assert.That(_queryCounter.GetCount(), Is.LessThanOrEqualTo(3));
}

Put this in your CI pipeline, and you’ve built a great safety net.

Why Does This Even Happen?

N+1 problems usually stem from a few common mistakes:

• Lazy Loading: This is the number one culprit. Using .UseLazyLoadingProxies() feels magical because related data just appears when you access it. But that “magic” is a new database query every time. It’s a footgun in web apps.
• Missing Include(): You simply forgot to tell EF Core to grab the related data. It’s an easy mistake to make when you’re focused on the business logic.
• Looping and Accessing Navigation Properties: The classic foreach loop where you touch a related entity (post.Author.Name) inside the loop, triggering a fetch for each one.

Okay, How Do We Fix It?

Once you’ve found the N+1, fixing it is usually straightforward. Here are the main strategies, from simplest to best.

1. Eager Loading with Include()

This is the quick fix. You tell EF Core exactly what related data to fetch in the initial query.

public async Task<List<Post>> GetPostsWithAuthorsAndComments()
{
    return await _context.Posts
        .Include(p => p.Author)
        .Include(p => p.Comments)
        .ToListAsync();
}

• Good for: When you need the full entity objects for some internal logic.
• The catch: It can easily over-fetch data. If your Author and Comments tables have 20 columns each but your API only needs one from each, you’re still pulling all of them across the wire.

2. Projecting to a DTO with Select()

This is the gold standard for APIs. Instead of pulling full entities, you shape the data into a Data Transfer Object (DTO) directly in the query.

public async Task<List<PostDto>> GetPostSummaries()
{
    return await _context.Posts
        .Select(p => new PostDto
        {
            Title = p.Title,
            AuthorName = p.Author.Name, // EF Core turns this into a JOIN
            CommentCount = p.Comments.Count()
        })
        .ToListAsync();
}

EF Core is smart enough to translate this Select statement into a single, efficient SQL query with the proper JOINs.

• Good for: Almost every API endpoint. It fetches only the data you need.
• The catch: It requires you to define a DTO, but that’s a good practice anyway.

3. Split Queries

Ever Include two or more collections (like Comments and Tags) and see your query performance die? You’ve hit a “cartesian explosion.” A single post with 10 comments and 10 tags results in 100 rows (10 * 10).

EF Core has a built-in fix for this: AsSplitQuery().

var posts = await _context.Posts
    .Include(p => p.Comments)
    .Include(p => p.Tags)
    .AsSplitQuery() // The magic is here
    .ToListAsync();

This tells EF Core to generate multiple queries (one for posts, one for comments, one for tags) and stitch the results together in memory. It’s way more efficient than a monstrous JOIN.

• Good for: Eager loading a parent with multiple child collections.
• The catch: It results in multiple database round-trips, but it’s almost always better than the cartesian explosion alternative.

The Final Takeaway: When to Use What

Here’s my mental checklist. No complex decision trees, just simple rules.

• When I’m building an API endpoint, I always start with projection (Select). It’s the most efficient and returns a clean data contract. This covers 90% of my use cases.
• I use eager loading (Include) when I need the full, tracked entities inside my service layer for complex business logic. But I’m very careful about what I include.
• If I need to Include more than one collection, I immediately reach for AsSplitQuery(). I don’t even think twice. It avoids a massive performance headache.
• I avoid lazy loading in production applications. I might use it for a quick proof-of-concept, but I disable it for any real project. The risk of an accidental N+1 is just too high.

The N+1 query isn’t a bug in EF Core; it’s a misunderstanding of how data loading works. By learning to spot it, fix it, and build automated checks, you can keep your application fast and your database happy.

References

• Loading Related Data - Microsoft Docs
• Single and Split Queries - Microsoft Docs
• Lazy Loading - Microsoft Docs

FAQ