How Things Work

Eager, Lazy & Explicit Loading

Three ways to load related data in EF Core — and why choosing wrong generates N+1 queries that destroy performance.

How It Works

EF Core gives you three ways to load related entities: Eager loading (.Include) fetches everything in one JOIN query upfront; Lazy loading fires additional queries automatically when you access a navigation property; Explicit loading lets you call .LoadAsync() manually when you need it. The right choice depends on whether you know which navigations you need, how large the collections are, and whether you're in a loop. Getting it wrong — especially lazy loading in a loop — generates N+1 queries that are completely invisible in the code.

Eager Loading: Include() Generates a Single JOIN Query

When you call .Include(o => o.Items), EF Core adds a LEFT JOIN to the generated SQL and materializes both the order and its items from a single result set. All data arrives in one round-trip. Best when you know upfront which navigations you need.

The Cartesian Explosion Problem with Multiple Includes

If an Order has 5 Items and 3 Tags, a single query with both .Include(Items) and .Include(Tags) returns 5 × 3 = 15 rows per order — the cartesian product. With 100 orders each having 10 items and 5 tags, that's 5,000 rows for what should be 1,500 entities. AsSplitQuery() splits this into separate queries.

Lazy Loading: SQL Fires When You Access a Navigation Property

With UseLazyLoadingProxies(), every navigation property access that isn't already loaded fires a new SQL SELECT. If you loop over 100 orders and access .Items on each one, you fire 101 queries. The danger is that the code looks identical to eager-loaded code — the N+1 is completely invisible.

Explicit Loading: You Control When Navigations Are Loaded

entry.Collection(o => o.Items).LoadAsync() lets you decide exactly when to load a navigation, after the entity is already in memory. Useful for conditional loads — you can check a flag before deciding to load the related collection, or load only filtered subsets using Query().

Projection: The Best Strategy for Read-Only Scenarios

Instead of loading full entities and navigations, use .Select() to project exactly the columns you need. 'o.Items.Count' in a Select becomes a COUNT() subquery — no navigation loading required. This is often 10x faster than any of the three loading strategies for read-only queries.

Key Concepts

🔗.Include() / .ThenInclude()

Eager loading operators. Added to the IQueryable before materialization. EF generates LEFT JOINs or separate queries (AsSplitQuery). Entities are tracked by default.

🔮UseLazyLoadingProxies()

Opt-in service registration that generates runtime proxy classes. Navigation property access fires SELECT automatically. Requires virtual keyword on all navigation properties.

📥.Load() / .LoadAsync()

Explicit loading method on DbContext.Entry(entity).Collection() or .Reference(). Fires a single SELECT for that specific navigation on demand.

✂️AsSplitQuery()

EF Core 5+ operator that splits a multi-Include query into separate SELECT statements. Eliminates cartesian product explosion. Trades 1 round-trip for N round-trips.

✅Collection.IsLoaded

Boolean flag on navigation collection entries indicating whether the collection has been loaded from the database. Check before loading to avoid duplicate queries.

🔍Filtered Includes

EF Core 5+: .Include(o => o.Items.Where(i => !i.IsCancelled)).Take(10) — load only a subset of a collection. Avoids loading entire large collections.

Loading strategies — patterns and anti-patterns

tsx

1// ✅ Eager loading — 1 query with JOIN (best for known navigations)
2var orders = await _dbContext.Orders
3    .Include(o => o.Customer)
4    .Include(o => o.Items)
5        .ThenInclude(i => i.Product)
6    .Where(o => o.Status == OrderStatus.Active)
7    .ToListAsync(cancellationToken);
8
9// ✅ EF Core 9: AsSplitQuery() for large Include chains
10// Avoids cartesian product explosion (Order × Items × Products)
11var orders = await _dbContext.Orders
12    .AsSplitQuery()     // 3 separate queries instead of 1 monster JOIN
13    .Include(o => o.Items)
14        .ThenInclude(i => i.Product)
15    .ToListAsync(cancellationToken);
16
17// ✅ Explicit loading — controlled, on-demand (best for conditional loads)
18var order = await _dbContext.Orders.FindAsync(orderId, cancellationToken);
19if (order.NeedsItemDetails)
20{
21    // Only load items when you actually need them
22    await _dbContext.Entry(order)
23        .Collection(o => o.Items)
24        .LoadAsync(cancellationToken);
25}
26
27// ❌ Lazy loading — INVISIBLE N+1 in loops
28// Requires: UseLazyLoadingProxies() + virtual navigation properties
29foreach (var order in orders)   // 10 orders loaded
30{
31    // This line fires a SQL query PER ORDER — you can't see it in this code!
32    var count = order.Items.Count;  // 10 additional SELECT queries = 11 total
33}
34
35// ✅ Fix: project what you need — no navigation loading at all
36var summaries = await _dbContext.Orders
37    .Select(o => new {
38        o.Id,
39        ItemCount = o.Items.Count,   // COUNT() subquery — stays server-side
40        Total = o.Items.Sum(i => i.Price * i.Quantity)
41    })
42    .ToListAsync(cancellationToken);

💡

Why This Matters

N+1 queries are the most common EF Core production performance problem. A dashboard that loads 200 orders and accesses .Items on each one in a loop fires 201 queries — and the code is indistinguishable from a single-query approach when using lazy loading. Understanding when each strategy fires SQL, and how to use projections to avoid loading navigations entirely, is the difference between a 50ms endpoint and a 12-second timeout.

Common Pitfalls

⚠Lazy loading in a loop is invisible N+1. Each navigation property access fires a SELECT. With 100 entities in a loop you get 101 queries. The code looks identical to eager-loaded code — only SQL Server Profiler or EF logging reveals the problem.

⚠Multiple .Include() chains on collections create cartesian products. Order with 10 Items and 10 ShippingHistory entries produces 100 rows per order in a single JOIN query. Use AsSplitQuery() when including more than one collection.

⚠AsNoTracking() disables lazy loading entirely — there is no EF proxy on the entity. Navigation properties on AsNoTracking entities will be null unless you explicitly Include() them or project them.

⚠Loading an entire entity graph with .Include() when you only need 2 fields from a related table is an invisible SELECT *. Always project with .Select() for read-only scenarios — skip loading navigations entirely.

Real-World Use Cases

1Lazy Loading Fired 1,847 Queries on a Single API Request

Scenario

Our order dashboard endpoint was taking 12 seconds to respond. SQL Server showed 1,847 separate SELECT statements within a single HTTP request. The application had lazy loading proxies enabled and the endpoint looped over orders.

Problem

The Razor view was iterating over a list of orders and accessing order.Customer.Name, order.Items.Count, and order.Items.Sum(i => i.Price) on each order. With lazy loading proxies, each of those property accesses fired a separate SQL query. With 180 orders on the dashboard, that was roughly 1 + 180 + 180 + 180 = 541... but Items was a collection accessed twice, triggering the load twice for the proxy. Some orders had multiple items loading per property. The actual query count was 1,847.

Solution

Projected the needed data in a single query: .Select(o => new DashboardDto { Id = o.Id, CustomerName = o.Customer.Name, ItemCount = o.Items.Count, Total = o.Items.Sum(i => i.Price * i.Quantity) }). This generated a single query with COUNT() and SUM() subqueries — down from 1,847 queries to 1 query, from 12 seconds to 80ms.

💡

Takeaway: Lazy loading is invisible N+1. The code reads identically to eager-loaded code. If you use lazy loading proxies, every navigation property access in a loop fires a query. Always use projections or eager loading for list/report endpoints.

2Cartesian Explosion: Include Chains Returning 50k Rows for 500 Orders

Scenario

An order export endpoint was working fine until a customer with large orders started using it. Their export of 500 orders was returning correctly but the query was pulling 50,000 rows and timing out. EF Core was returning correct data — but the method to get it was catastrophically wrong.

Problem

The query used .Include(o => o.Items).ThenInclude(i => i.Product).Include(o => o.ShippingHistory). Orders had an average of 8 items and 12.5 shipping history entries. The JOIN produced 8 × 12.5 = 100 rows per order. 500 orders × 100 rows = 50,000 rows transferred for 500 orders with 4,000 items and 6,250 history entries. SQL Server was doing the work but the network transfer was massive.

Solution

Added AsSplitQuery() to the IQueryable. EF Core now fires 3 separate queries: one for orders, one for items+products (WHERE OrderId IN (...)), one for shipping history. Total rows: 500 + 4,000 + 6,250 = 10,750 — 80% reduction. Query time dropped from 8 seconds to 600ms.

💡

Takeaway: Multiple .Include() chains on collections create cartesian products. 3 collections of average size 10 on 1000 rows = 1,000,000 result rows. Use AsSplitQuery() when you have more than one collection Include, or when cardinality is unknown.

3Include() on AsNoTracking() Detached Entities Missing Collections

Scenario

A background job loaded products with AsNoTracking() for a bulk pricing update, then tried to access product.Category.Name for logging. Category was always null even though categories existed in the database and the product had a CategoryId.

Problem

The query used AsNoTracking() without Include(): 'await _dbContext.Products.AsNoTracking().ToListAsync()'. Since AsNoTracking() skips the identity map and change tracker, EF cannot lazy-load navigations on detached entities — there's no proxy, no entry, no DbContext attached to them. product.Category was null because it was never loaded.

Solution

Either add .Include(p => p.Category) to the original query — even with AsNoTracking() this generates a JOIN and loads the navigation. Or project the needed data: .Select(p => new { p.Id, p.Price, CategoryName = p.Category.Name }).ToList(). Lazy loading requires a tracked entity with a DbContext reference — it never works on AsNoTracking entities.

💡

Takeaway: Lazy loading does not work on AsNoTracking entities. There is no proxy, no DbContext reference, and no identity map to trigger a load. Always use .Include() or projection when you need navigations in AsNoTracking queries.