.NET & C# Development · Lesson 29 of 92

Lazy vs Eager Loading — What's Silently Killing Performance?

The N+1 Problem Explained

You have a Blog that has many Posts. You load 10 blogs and loop through them to access their posts:

var blogs = await db.Blogs.ToListAsync(); // 1 query

foreach (var blog in blogs)
{
    Console.WriteLine($"{blog.Title}: {blog.Posts.Count} posts"); // N queries
}

EF fires 1 query for the blogs, then 1 query per blog to load its posts — 11 queries total for 10 blogs. With 100 blogs: 101 queries. With 1000: 1001 queries. The app appears to work fine with a small dataset and then collapses under load.

SQL

SELECT * FROM Blogs;
SELECT * FROM Posts WHERE BlogId = 1;
SELECT * FROM Posts WHERE BlogId = 2;
-- ... 8 more

Eager Loading with Include / ThenInclude

Load related data in the same query using JOIN:

var blogs = await db.Blogs
    .Include(b => b.Posts)
    .ToListAsync();
// Single query with JOIN — all Posts loaded

For deeper graphs, chain ThenInclude:

var blogs = await db.Blogs
    .Include(b => b.Posts)
        .ThenInclude(p => p.Comments)
            .ThenInclude(c => c.Author)
    .ToListAsync();

This generates a single SQL query with multiple JOINs. The risk: if Posts has 1000 rows and each has 50 Comments, you're pulling 50,000 rows across the wire just to populate a list of blogs.

// Multiple Includes on the same entity
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(i => i.Product)
    .Where(o => o.Status == "Pending")
    .ToListAsync();

Split Queries for Large Includes

When a single JOIN query produces a cartesian explosion (many rows × many rows), split it into separate queries:

var blogs = await db.Blogs
    .Include(b => b.Posts)
        .ThenInclude(p => p.Comments)
    .AsSplitQuery()   // 3 separate queries instead of one massive JOIN
    .ToListAsync();

EF runs one query per table, then stitches the results in memory. Trade-off: multiple round trips, but far fewer rows transferred. Use when any include collection could be large.

Set split query as the global default:

options.UseSqlServer(connectionString, sql =>
    sql.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Explicit Loading

Load related data on demand, after the parent is already in memory:

var blog = await db.Blogs.FindAsync(blogId);

// Load collection explicitly
await db.Entry(blog)
        .Collection(b => b.Posts)
        .LoadAsync();

// Load reference explicitly
await db.Entry(blog)
        .Reference(b => b.Owner)
        .LoadAsync();

// Load with a filter — only recent posts
await db.Entry(blog)
        .Collection(b => b.Posts)
        .Query()
        .Where(p => p.CreatedAt > DateTime.UtcNow.AddDays(-7))
        .LoadAsync();

Useful when you conditionally need related data and don't want to pay for it upfront. Still N+1 if used in a loop — reserve it for single-entity operations.

Lazy Loading With Proxies (and Why It's Dangerous)

EF Core supports lazy loading via runtime proxies. Navigation properties are loaded automatically the first time you access them.

Setup:

// Install: Microsoft.EntityFrameworkCore.Proxies
options.UseLazyLoadingProxies()
       .UseSqlServer(connectionString);

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public virtual ICollection<Post> Posts { get; set; } = new List<Post>(); // must be virtual
}

Now accessing blog.Posts fires a SQL query automatically:

var blog = await db.Blogs.FindAsync(blogId);
var count = blog.Posts.Count; // query fires here, silently

Why it's dangerous:

Queries fire inside Razor views, serializers, or anywhere a nav property is accessed — invisible to the developer
N+1 problems are completely silent and only visible in logs or profilers
The DbContext must be alive when the lazy load fires — accessing a detached entity throws
Serializing an entity graph (e.g., returning from a controller) can trigger hundreds of lazy queries

The rule: use lazy loading only in desktop/local applications where query count doesn't matter. Never in web APIs.

AsNoTracking for Read-Only Queries

By default, EF tracks every loaded entity in the ChangeTracker. This costs memory and CPU time. For read-only queries (GET endpoints, reports), disable it:

var blogs = await db.Blogs
    .AsNoTracking()
    .Include(b => b.Posts)
    .ToListAsync();

Or set it globally for a context instance:

db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

AsNoTrackingWithIdentityResolution is a middle ground — no tracking overhead but EF still deduplicates entities when the same row appears in multiple joins:

var blogs = await db.Blogs
    .AsNoTrackingWithIdentityResolution()
    .Include(b => b.Posts)
    .ToListAsync();

Projecting to DTOs With Select — The Best Approach

For API responses, don't load full entities at all. Project directly to DTOs in SQL using Select():

var blogDtos = await db.Blogs
    .Where(b => b.IsPublished)
    .Select(b => new BlogSummaryDto
    {
        Id        = b.Id,
        Title     = b.Title,
        PostCount = b.Posts.Count,                     // translated to COUNT(*)
        LastPost  = b.Posts.Max(p => p.CreatedAt)      // translated to MAX()
    })
    .ToListAsync();

This generates a single, efficient SQL query:

SQL

SELECT b.Id, b.Title,
       COUNT(p.Id) AS PostCount,
       MAX(p.CreatedAt) AS LastPost
FROM Blogs b
LEFT JOIN Posts p ON p.BlogId = b.Id
WHERE b.IsPublished = 1
GROUP BY b.Id, b.Title

No navigation property loading, no change tracking, no cartesian products. This is the right default for GET endpoints.

Nested projections work too:

var orders = await db.Orders
    .Where(o => o.Status == "Confirmed")
    .Select(o => new OrderDto
    {
        Id           = o.Id,
        CustomerName = o.Customer.Name,  // JOIN to Customer table
        Items = o.OrderItems.Select(i => new OrderItemDto
        {
            ProductName = i.Product.Name,
            Quantity    = i.Quantity,
            UnitPrice   = i.UnitPrice
        }).ToList()
    })
    .ToListAsync();

Loading Strategy Decision Tree

Need to save changes?
├── YES → Load with tracking (default)
│         Use Include for navigation properties you'll modify
└── NO  → Use AsNoTracking()
          Need only a subset of columns?
          ├── YES → Project with Select() to a DTO   ← prefer this
          └── NO  → Include() / AsNoTrackingWithIdentityResolution()

In a loop over multiple entities?
├── YES → ALWAYS use Include() or Select()  ← never access nav props in a loop without eager loading
└── NO  → Explicit loading is fine for a single entity

Detecting N+1 in Development

Enable detailed query logging:

options.UseSqlServer(connectionString)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging(); // dev only

Or use the MiniProfiler.AspNetCore.EF package to see every query fired per request in a UI overlay. If you see the same query template repeated N times, you have an N+1.

Intercept Every Save — Add Audit Trails Automatically

Next Lesson

Challenge: Find & Fix the N+1 Query in This Controller