Solving the N+1 Problem: Eager vs. Lazy Loading in EF Core

The Hidden Cost of On-Demand Data

You write a simple loop to display a list of Orders and their associated Customer names. The code looks clean, but your database server is screaming. In the logs, you see one query to fetch 50 orders, followed by 50 individual queries to fetch each customer. This is the N+1 Query Problem.

The takeaway is simple: while loading data "on-demand" feels convenient, it often transforms a single efficient database trip into a performance bottleneck. Choosing the right loading strategy in Entity Framework Core (EF Core) is the primary way to control this behavior.

Eager Loading: The Single-Trip Approach

Eager loading tells EF Core to fetch related data as part of the initial query using JOIN statements. You implement this using the .Include() and .ThenInclude() methods.

This is generally the safest default for web APIs. Since APIs typically serialize data to JSON, any property not loaded before the database context is disposed will either be null or trigger an exception. By explicitly including dependencies, you ensure the data is present in one round-trip.

Lazy Loading: The On-Demand Trade-off

Lazy loading defers the loading of related data until the property is actually accessed in your code. To use this, you must install the Microsoft.EntityFrameworkCore.Proxies package and mark your navigation properties as virtual.

While this keeps the initial query small, it is dangerous in loops. If you iterate through a collection of 100 entities and access a virtual property on each, EF Core will execute 100 additional queries. This is why lazy loading is often discouraged in high-traffic production environments or when using serializers that automatically traverse every property of an object.

Worked Example: Optimized Data Retrieval

Consider a scenario where we need to retrieve a list of Blog entities, their Posts, and the Author of each post. We want to avoid the Cartesian product (where the result set grows exponentially) by using Split Queries.

// Run this within a service or controller with access to the DbContext
// Required: EF Core 5.0+ for Filtered Include and Split Queries

var blogs = await _context.Blogs
    .AsSplitQuery() // Prevents Cartesian explosion by using separate SELECTs
    .Include(b => b.Posts
        .Where(p => p.IsPublished)) // Filtered Include: only get published posts
    .ThenInclude(p => p.Author)
    .ToListAsync();

Analysis of this configuration:

• .AsSplitQuery(): Instead of one massive JOIN that duplicates Blog data for every Post, EF Core executes a few targeted queries and stitches them together in memory.
• .Where() inside .Include(): This reduces the data transferred from the database by filtering the related collection at the SQL level rather than in memory.

Decision Matrix: Which to Choose?

Strategy	Best Use Case	Primary Risk	Requirement
Eager Loading	Web APIs, known data requirements	Cartesian Explosion	.Include()
Lazy Loading	Rapid prototyping, complex UI trees	N+1 Query Problem	virtual properties
Explicit Loading	Conditional loading based on logic	Multiple round-trips	.Entry().Load()

Limitations and Verification

The biggest limitation of Eager Loading is the Cartesian Product. When you include multiple one-to-many collections (e.g., a Blog with many Posts AND many Tags), the database returns a result set where the Blog data is repeated for every combination of Post and Tag. This can crash your application due to memory exhaustion.

How to verify your choice:

1. Enable Sensitive Data Logging in your DbContext options during development.
2. Check the Debug console. If you see a flood of SELECT statements while looping through a list, you have an N+1 problem.
3. If you see a single query with five or six LEFT JOIN statements and a massive amount of redundant text, switch to .AsSplitQuery().

Actionable Closing

Stop relying on the "magic" of lazy loading in production. Start by using Eager Loading with .Include() for your primary data needs. If your queries become too heavy due to multiple collections, apply .AsSplitQuery() to balance the load between the database and the application memory.