Loading Strategies: Eager, Lazy, Explicit

Overview

Understanding when and how to load related data is critical for performance. EF Core provides three main strategies.

Eager Loading

Basic Include

// Load blogs with posts in single query
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .ToListAsync();

// Generated SQL:
// SELECT b.*, p.*
// FROM Blogs b
// LEFT JOIN Posts p ON b.Id = p.BlogId

Multiple Includes

// Multiple navigation properties
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .ToListAsync();

Nested Includes (ThenInclude)

// Include nested levels
var blogs = await context.Blogs
    .Include(b => b.Posts)
        .ThenInclude(p => p.Author)
    .Include(b => b.Posts)
        .ThenInclude(p => p.Comments)
    .ToListAsync();

Filtered Include (EF Core 5+)

// Include only published posts
var blogs = await context.Blogs
    .Include(b => b.Posts.Where(p => p.IsPublished))
    .ToListAsync();

// Include with ordering
var blogs = await context.Blogs
    .Include(b => b.Posts.OrderByDescending(p => p.Date))
    .ToListAsync();

Split Query (EF Core 5+)

// Avoid cartesian explosion
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Authors)
    .AsSplitQuery()
    .ToListAsync();

// Executes 3 queries instead of 1 huge JOIN:
// SELECT * FROM Blogs
// SELECT * FROM Posts WHERE BlogId IN (...)
// SELECT * FROM Authors WHERE BlogId IN (...)

Lazy Loading

Setup (Not Recommended)

// Install package
// Microsoft.EntityFrameworkCore.Proxies

// Enable in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies();
}

// Make navigation properties virtual
public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Post> Posts { get; set; }  // Virtual!
}

Usage

var blog = await context.Blogs.FirstAsync();
// Posts not loaded yet

var posts = blog.Posts;  // Triggers query here!
// SELECT * FROM Posts WHERE BlogId = @p0

Problems with Lazy Loading

// ❌ Creates N+1 problem!
var blogs = await context.Blogs.ToListAsync();

foreach (var blog in blogs)
{
    // Each access triggers a query
    Console.WriteLine($"{blog.Name} has {blog.Posts.Count} posts");
    // N queries executed!
}

// ❌ Can't use after DbContext disposed
using (var context = new AppDbContext())
{
    var blog = await context.Blogs.FirstAsync();
}
// blog.Posts throws exception - context disposed!

// ❌ Serialization issues
return Ok(blogs);  // Lazy loading triggers during JSON serialization!

Explicit Loading

Load Collections

// Load blog first
var blog = await context.Blogs.FirstAsync();

// Explicitly load posts
await context.Entry(blog)
    .Collection(b => b.Posts)
    .LoadAsync();

// Posts now available
Console.WriteLine(blog.Posts.Count);

Load References

var post = await context.Posts.FirstAsync();

// Load related blog
await context.Entry(post)
    .Reference(p => p.Blog)
    .LoadAsync();

Filtered Explicit Loading

await context.Entry(blog)
    .Collection(b => b.Posts)
    .Query()
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.Date)
    .LoadAsync();

Check if Loaded

var isLoaded = context.Entry(blog)
    .Collection(b => b.Posts)
    .IsLoaded;

if (!isLoaded)
{
    await context.Entry(blog)
        .Collection(b => b.Posts)
        .LoadAsync();
}

Projection (Best Performance)

Basic Projection

// Only select needed data
var blogDtos = await context.Blogs
    .Select(b => new BlogDto
    {
        Id = b.Id,
        Name = b.Name,
        PostCount = b.Posts.Count,
        LatestPost = b.Posts
            .OrderByDescending(p => p.Date)
            .Select(p => p.Title)
            .FirstOrDefault()
    })
    .ToListAsync();

// Benefits:
// - Single optimized query
// - Only needed columns
// - No tracking overhead
// - Smaller result set

Complex Projections

var orderSummaries = await context.Orders
    .Select(o => new OrderSummaryDto
    {
        OrderId = o.Id,
        OrderDate = o.OrderDate,
        CustomerName = $"{o.Customer.FirstName} {o.Customer.LastName}",
        TotalAmount = o.OrderItems.Sum(oi => oi.Quantity * oi.UnitPrice),
        ItemCount = o.OrderItems.Count,
        Items = o.OrderItems.Select(oi => new OrderItemDto
        {
            ProductName = oi.Product.Name,
            Quantity = oi.Quantity,
            Price = oi.UnitPrice
        }).ToList()
    })
    .ToListAsync();

Strategy Comparison

Performance Benchmark

[MemoryDiagnoser]
public class LoadingBenchmark
{
    [Benchmark(Baseline = true)]
    public async Task<int> EagerLoading()
    {
        var blogs = await _context.Blogs
            .Include(b => b.Posts)
            .ToListAsync();
        return blogs.Sum(b => b.Posts.Count);
    }

    [Benchmark]
    public async Task<int> LazyLoading()
    {
        var blogs = await _context.Blogs.ToListAsync();
        return blogs.Sum(b => b.Posts.Count);  // Lazy loads
    }

    [Benchmark]
    public async Task<int> ExplicitLoading()
    {
        var blogs = await _context.Blogs.ToListAsync();
        foreach (var blog in blogs)
        {
            await _context.Entry(blog)
                .Collection(b => b.Posts)
                .LoadAsync();
        }
        return blogs.Sum(b => b.Posts.Count);
    }

    [Benchmark]
    public async Task<int> Projection()
    {
        var counts = await _context.Blogs
            .Select(b => b.Posts.Count)
            .ToListAsync();
        return counts.Sum();
    }
}

// Results (100 blogs, 1000 posts):
// EagerLoading:    150ms, 10MB memory
// LazyLoading:   2,500ms, 12MB memory (N+1!)
// ExplicitLoading: 300ms, 11MB memory (still N+1)
// Projection:       50ms,  1MB memory (best!)

Decision Tree

Need related data?
├─ Read-only/Display?
│  └─ Use Projection (Select) ✅ Fastest
│
├─ Need to modify entities?
│  ├─ One collection? → Use Include
│  ├─ Multiple collections? → Use AsSplitQuery
│  └─ Conditional? → Use filtered Include
│
├─ Don't know upfront?
│  └─ Use Explicit Loading (load on demand)
│
└─ Never use Lazy Loading (causes N+1)

Real-World Scenarios

API Endpoint - List View

// ✅ BEST: Projection for list
[HttpGet]
public async Task<IActionResult> GetProducts()
{
    var products = await _context.Products
        .Select(p => new ProductListDto
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price,
            CategoryName = p.Category.Name,
            ImageUrl = p.Images
                .Where(i => i.IsPrimary)
                .Select(i => i.Url)
                .FirstOrDefault()
        })
        .ToListAsync();

    return Ok(products);
}

API Endpoint - Detail View

// ✅ GOOD: Include for detail with modifications
[HttpGet("{id}")]
public async Task<IActionResult> GetProduct(int id)
{
    var product = await _context.Products
        .Include(p => p.Category)
        .Include(p => p.Images)
        .Include(p => p.Reviews)
            .ThenInclude(r => r.Customer)
        .AsSplitQuery()
        .FirstOrDefaultAsync(p => p.Id == id);

    if (product == null) return NotFound();

    // Might modify and save
    product.ViewCount++;
    await _context.SaveChangesAsync();

    return Ok(product);
}

Conditional Loading

// Load basic data first
var order = await _context.Orders
    .AsNoTracking()
    .FirstOrDefaultAsync(o => o.Id == orderId);

// Load details only if needed
if (includeItems)
{
    await _context.Entry(order)
        .Collection(o => o.OrderItems)
        .Query()
        .Include(oi => oi.Product)
        .LoadAsync();
}

if (includeCustomer)
{
    await _context.Entry(order)
        .Reference(o => o.Customer)
        .LoadAsync();
}

Best Practices

✅ DO

// Use projection for read-only
var dtos = await context.Orders
    .Select(o => new OrderDto { ... })
    .ToListAsync();

// Use Include for entities you'll modify
var order = await context.Orders
    .Include(o => o.Items)
    .FirstAsync();

// Use AsSplitQuery for multiple collections
var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Authors)
    .AsSplitQuery()
    .ToListAsync();

// Use filtered Include when possible
var activeBlogs = await context.Blogs
    .Include(b => b.Posts.Where(p => p.IsPublished))
    .ToListAsync();

❌ DON'T

// Don't use lazy loading (N+1 risk)
optionsBuilder.UseLazyLoadingProxies();

// Don't load everything
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
            .ThenInclude(p => p.Category)
    .ToListAsync();  // Huge query!

// Don't use Include for read-only
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();  // Use projection instead!

// Don't forget AsSplitQuery with multiple collections
var result = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Tags)
    .ToListAsync();  // Cartesian explosion!

Interview Questions

Q: What are the three loading strategies in EF Core?

A:

1. Eager Loading: Load related data with Include() in the initial query
2. Lazy Loading: Automatically load related data when accessed (not recommended)
3. Explicit Loading: Manually load related data after the initial query with Entry().Collection().Load()

Q: Why is lazy loading not recommended?

A: Lazy loading can cause N+1 query problems, doesn't work after DbContext is disposed, and can trigger unexpected queries during serialization. Eager loading or projection are better alternatives.

Q: When should you use AsSplitQuery?

A: When including multiple collections to avoid cartesian explosion. Split query executes separate queries for each collection instead of one large JOIN.

Q: What's the difference between Include and projection?

A: Include loads full entities with change tracking (for modifications). Projection loads only needed data into DTOs without tracking (faster, read-only).

Additional Resources

• Loading Related Data
• Split Queries
• Projection