Deferred Execution Patterns

In this lesson, you will learn how to master Deferred Execution to prevent "N+1" problems and unnecessary database roundtrips in Entity Framework Core.

The Concept of Laziness

In EF Core, writing a LINQ query does not immediately fetch data from your database. Instead, EF Core builds an "Expression Tree" that represents the query. The database is only hit when you trigger materialization—the moment you force EF Core to execute the query and map the results into C# objects.

Common triggers for materialization include:

• Calling ToList(), ToArray(), or ToDictionary().
• Iterating over the query in a foreach loop.
• Calling aggregate methods like FirstOrDefault(), Single(), or Count().

Minimizing Roundtrips

A common mistake for intermediate developers is triggering materialization inside a loop. Consider this scenario:

// BAD: Multiple roundtrips
var categories = context.Categories.ToList(); 
foreach (var category in categories)
{
    // Accessing a navigation property inside a loop triggers a new query each time!
    var products = context.Products.Where(p => p.CategoryId == category.Id).ToList();
    Console.WriteLine($"{category.Name}: {products.Count} products");
}

In the example above, if you have 10 categories, you perform 11 database roundtrips (1 for the categories, and 10 individual queries for the products).

By understanding deferred execution, you can compose your query to fetch exactly what you need in one go using .Include() (if the relationship is pre-defined) or projected types. Even if you don't use Include, knowing when the data is pulled allows you to aggregate your filtering logic into a single LINQ statement before the materialization occurs.

How to Monitor

Because execution is deferred, the code you write in your repository might look like a single line, but the actual database work happens elsewhere. Always be mindful of where your code finally calls .ToList() or similar methods. If you suspect you are hitting the database too often, utilize Database Interceptors (which you've learned previously) to log the actual SQL commands being sent; you will often be surprised by how many hidden queries are triggered by simple loops.

Key Takeaway: Deferred execution means your LINQ queries are just instructions—database interaction only occurs at the exact moment you force the results to materialize, so avoid triggering that materialization inside loops.