For my latest client, I’ve been working with C# and ASP.NET Core, using Entity Framework (EF) Core as the ORM. This gave me the chance to explore how relationships between entities are modeled and and how EF Core loads related data.
Choosing the right data loading strategy directly impacts the database queries, resource consumption, response times, and even code clarity.
These are the main strategies:
- Eager - related entities are loaded together with the parent ones
- Explicit - related entities are loaded when you decide to load them
- Lazy - related entities are loaded when you try and access them
I’ll illustrate the difference with practical code examples.
The project
I setup a Warehouse API with these entities:
namespace Warehouse.Api.Entities;
public class Customer
{
public int Id { get; set;}
public string Name { get; set; }
public string Email { get; set; }
private ICollection<Order>? _orders;
public virtual ICollection<Order> Orders => _orders ??= [];
}
public class Order
{
public int Id { get; set; }
public int CustomerId { get; set; }
public virtual Customer Customer { get; set; } = null!;
public virtual List<Item> Items { get; init; } = [];
public virtual ICollection<OrderItem> OrderItems { get; init; } = [];
}
public class OrderItem
{
public int Id { get; set; }
public int OrderId { get; set; }
public virtual Order Order{ get; set; } = null!;
public int ItemId { get; set; }
public virtual Item Item { get; set; } = null!;
public int Quantity { get; set; } = 1;
}
public class Item
{
public int Id { get; set; }
public required string Name { get; set; }
public required decimal Price { get; set; }
}
This API has a CustomerService that handles fetching the Customer with their data from the DB. It has a method for each loading type, implementing the interface:
namespace Warehouse.Api.Services;
using Entities;
public interface ICustomerService
{
Task<List<Customer>> GetCustomersEagerAsync();
Task<List<Customer>> GetCustomersExplicitAsync();
Task<List<Customer>> GetCustomersLazyAsync();
}
I also added some extension methods to (pretty) print the records:
public static void Print(this Order order)
{
Console.WriteLine($" Order #{order.Id}");
}
The strategies
Eager Loading
This approach loads all related data in a single database query. Eager loading prevents additional round-trips to the database.
In EF Core, this is achieved with the Include() method to load the child entity, followed by ThenInclude() if loading any nested entities. Under the hood, EF Core translates this into SQL JOIN operations.
public async Task<List<Customer>> GetCustomersEagerAsync()
{
Console.WriteLine("~~~ EAGER LOADING START ~~~");
var customers = await _dbContext.Customers
.Include(c => c.Orders)
.ThenInclude(o => o.OrderItems)
.ThenInclude(o => o.Item)
.AsNoTracking()
.AsSplitQuery()
.ToListAsync();
// All the data already loaded
Console.WriteLine("~~~ EAGER LOADING END ~~~");
return customers;
}
To optimize performance, I added the AsNoTracking() method. This pulls the data in a “read-only” mode and speeds up queries. It skips setting up the change tracker.
Note for further reference, when adding more same-level JOINs (multiple Include()), it is recommended to use the AsSplitQuery to avoid cartesian explosion.
Explicit Loading
This allows developers to decide exactly when to load data. Calling the Load method triggers the ORM to query for the navigational property. Explicit loading offers fine-grained control of when to load data based on runtime conditions.
The helper methods used for different scenarios:
-
Reference()- single navigation property -
Collection()- collection
public async Task<List<Customer>> GetCustomersExplicitAsync()
{
Console.WriteLine("~~~ EXPLICIT LOADING START ~~~");
var customers = await _dbContext.Customers.ToListAsync();
foreach (var customer in customers)
{
if (customer.Id <= 3)
{
// Explicitly load the orders
await _dbContext.Entry(customer).Collection(o => o.Orders).LoadAsync();
}
customer.Print();
foreach (var order in customer.Orders)
{
order.Print();
if (order.Id <= 4)
{
// Also explicitly load the orderItems
await _dbContext.Entry(order).Collection(o => o.OrderItems).LoadAsync();
}
foreach (var orderItem in order.OrderItems)
{
// Explicitly load the single navigation property - item
await _dbContext.Entry(orderItem).Reference(o => o.Item).LoadAsync();
orderItem.Print();
}
}
}
Console.WriteLine("~~~ EXPLICIT LOADING END ~~~");
return customers;
}
Lazy Loading
The main entity is loaded first, and the related ones are only loaded when the navigation property is accessed.
The ORM makes use of proxies (dynamic classes) to trigger queries as they intercept any access to the navigation property.
The proxies are not enabled by default, so for this, I installed the Microsoft.EntityFrameworkCore.Proxies NuGet package. Then, I included the option to enable lazy loading:
builder.Services.AddDbContext<WarehouseDbContext>(options =>
{
options.UseLazyLoadingProxies()
.UseSqlite(connString);
});
I also had to change the entities from sealed record to class, and change some navigational properties by adding the virtual keyword.
public async Task<List<Customer>> GetCustomersLazyAsync()
{
Console.WriteLine("~~~ LAZY LOADING START ~~~");
var customers = await _dbContext.Customers
.ToListAsync();
foreach (var customer in customers)
{
customer.Print();
foreach (var order in customer.Orders) // Orders accessed, loading Orders
{
order.Print();
foreach (var items in order.Items) // Now loading OrderItems
{
items.Print();
}
}
}
Console.WriteLine("~~~ LAZY LOADING END ~~~");
return customers;
}
This might be problematic, triggering the infamous N+1 problem. Here, we execute repeated database queries each time we access the navigation property, this can easily grow to a high number of queries to the database, increasing execution time and workload. Eventually, this might bring the database to a halt.
Let’s say we have this number of records together with the amount of queries we produce:
10 customers - 1 Customer query
10 orders - 10 Order queries
5 items - 100 Items queries
Total of 111 queries
So we will execute 111 database queries to fetch the ten customers with their orders and items.
Which one to use?
The decision to pick one over the other depends on various factors like the database used, the data access patterns, performance requirements, how much data to load upfront, etc.
Eager loading is great when you know upfront what related data you need. Or for when the related data is (almost) always accessed. It minimizes the queries, which makes the access more predictable. But it may fetch data that’s not being used, and it has a larger initial query.
Explicit loading is useful for conditional or data retrieval based on user actions (ex: pressing a “Show Details” button). It offers the most control over data loading.
But all that manual loading requires more complex/maybe ugly code. It is easier to get null references when you forget to load some data. And it is also susceptible to the N+1 issue.
Usually, you want to avoid Lazy Loading in favor of eager loading, especially if you always access the nested data. Lazy loading creates unnecessary data round-trips and makes performance issues harder to debug. It doesn’t work with AsNoTracking() and requires changing the entities (virtual) and proxy support. As the codebase grows and more data relations are added, it requires monitoring for N+1 issues.
That said, lazy loading has some strengths. It provides a faster initial load (main entity), keeps the code simpler, and it’s favored when related data is rarely accessed. It is also convenient for rapid development.
For embedded databases like SQLite, the cost of individual queries is much lower compared to client-server databases. Since SQLite doesn’t have the network overhead, the performance impact of lazy loading is reduced.
In large applications, the usual choice is a combination of these strategies. Each scenario has different access patterns and constraints. So picking the strategy case by case is better then relying on one strategy alone.