Loading Strategies

What are Loading Strategies?

Loading strategies determine when and how related data is loaded from the database.

Strategy	When Loaded	Use Case
Eager Loading	With initial query	Always need related data
Lazy Loading	When accessed	Sometimes need related data
Explicit Loading	Manually triggered	Conditional loading
Select Loading	With projection	Specific properties only

Eager Loading

Eager loading loads related data as part of the initial query using Include().

Basic Include

// Load products with their categories
var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

// Now you can access category without additional queries
foreach (var product in products)
{
    Console.WriteLine($"{product.Name} - {product.Category.Name}");
}

Multiple Includes

var products = await _context.Products
    .Include(p => p.Category)
    .Include(p => p.Tags)
    .ToListAsync();

ThenInclude (Nested)

// Load orders with items and products
var orders = await _context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

// Multiple ThenIncludes
var orders = await _context.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
            .ThenInclude(p => p.Category)
    .ToListAsync();

Filtered Include (EF Core 5+)

// Only include products with quantity > 0
var categories = await _context.Categories
    .Include(c => c.Products.Where(p => p.Quantity > 0))
    .ToListAsync();

// Order related data
var categories = await _context.Categories
    .Include(c => c.Products.OrderBy(p => p.Name))
    .ToListAsync();

Lazy Loading

Lazy loading automatically loads related data when you access the navigation property.

Setup

dotnet add package Microsoft.EntityFrameworkCore.Proxies

builder.Services.AddDbContext<InvenTrackDbContext>(options =>
    options.UseSqlServer(connectionString)
        .UseLazyLoadingProxies());

Make Navigation Properties Virtual

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public int CategoryId { get; set; }

    // Virtual enables lazy loading
    public virtual Category Category { get; set; } = null!;
}

Usage

// Load product without category
var product = await _context.Products.FindAsync(1);

// Accessing Category triggers a database query
var categoryName = product.Category.Name; // Query executed here

Explicit Loading

Explicit loading gives you manual control over when to load related data.

// Load product
var product = await _context.Products.FindAsync(1);

// Explicitly load category
await _context.Entry(product)
    .Reference(p => p.Category)
    .LoadAsync();

// Explicitly load collection
var category = await _context.Categories.FindAsync(1);

await _context.Entry(category)
    .Collection(c => c.Products)
    .LoadAsync();

Filtered Explicit Loading

var category = await _context.Categories.FindAsync(1);

// Load only products with quantity > 0
await _context.Entry(category)
    .Collection(c => c.Products)
    .Query()
    .Where(p => p.Quantity > 0)
    .LoadAsync();

// Get count without loading
var productCount = await _context.Entry(category)
    .Collection(c => c.Products)
    .Query()
    .CountAsync();

Select Loading (Projection)

Select loading loads only the specific properties you need.

// Load only needed properties
var products = await _context.Products
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.Price,
        CategoryName = p.Category.Name // Only category name
    })
    .ToListAsync();

The N+1 Problem

❌ Bad: N+1 Queries

// 1 query to get products
var products = await _context.Products.ToListAsync();

// N queries (one per product) to get categories
foreach (var product in products)
{
    Console.WriteLine(product.Category.Name); // Lazy load triggers query
}

// Total: 1 + N queries!

✅ Good: Single Query

// Single query with JOIN
var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

foreach (var product in products)
{
    Console.WriteLine(product.Category.Name); // No additional query
}

// Total: 1 query!

Complete InvenTrack Example

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly InvenTrackDbContext _context;

    public ProductsController(InvenTrackDbContext context)
    {
        _context = context;
    }

    // Eager loading: Always need category
    [HttpGet]
    public async Task<ActionResult> GetProducts()
    {
        var products = await _context.Products
            .Include(p => p.Category)
            .ToListAsync();

        return Ok(products);
    }

    // Select loading: Only need specific properties
    [HttpGet("summary")]
    public async Task<ActionResult> GetProductSummary()
    {
        var products = await _context.Products
            .Select(p => new
            {
                p.Id,
                p.Name,
                p.Price,
                CategoryName = p.Category.Name
            })
            .ToListAsync();

        return Ok(products);
    }

    // Explicit loading: Conditionally load related data
    [HttpGet("{id}")]
    public async Task<ActionResult> GetProduct(int id, [FromQuery] bool includeCategory = false)
    {
        var product = await _context.Products.FindAsync(id);

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

        // Explicitly load category if requested
        if (includeCategory)
        {
            await _context.Entry(product)
                .Reference(p => p.Category)
                .LoadAsync();
        }

        return Ok(product);
    }
}

When to Use Each Strategy

Strategy	When to Use	Pros	Cons
Eager	Always need related data	Single query, no N+1	May load unnecessary data
Lazy	Sometimes need data	Load on demand	N+1 problem, many queries
Explicit	Conditional loading	Full control	More code
Select	Specific properties only	Most efficient	Can't update entities

Best Practices

• Prefer eager loading: For collections to avoid N+1
• Use Select: When you only need specific properties
• Avoid lazy loading: In web APIs (can cause issues)
• Explicit loading: For conditional scenarios
• Profile queries: Use logging to see generated SQL
• AsNoTracking(): For read-only queries (better performance)
• Filtered includes: Reduce data transfer

Key Takeaways

• Eager loading: Include() loads related data upfront
• Lazy loading: Loads when accessed (requires virtual)
• Explicit loading: Manual control with Entry().Load()
• Select loading: Project specific properties
• N+1 problem: Avoid with eager loading
• ThenInclude(): Load nested relationships
• Filtered includes: Where/OrderBy on related data
• Choose wisely: Based on your specific use case