WAQS Querying: Lazy loading

7 reasons to use WAQS

WAQS documentation


Lazy loading trap

In my missions, I make a lot of audits on EF performance issues and lazy laoding is the cause of about 45% of them.

The first trap about lazy-loading is the “N+1” problem.

Imagine this code with lazy loading enabled:

foreach (var c in _context.Customers)
    double amount = 0;
    foreach (var o in c.Orders)
        foreach (var od in o.OrderDetails)
            amount += od.Quantity * od.UnitPrice;
    // Do something

Entity Framework executes one query to load the customers, one query per customer to load customer’s Orders and one query per order to load order’s OrderDetails.

So it means 1 + n + n² queries!

If we have 100 customers with 10 orders for each of them with 10 details, it means 1101 SQL queries with a very small volumetry. Now, if you have 1 000 000 customers, it means 11 000 001 SQL queries!

And yes, I already saw it in real world and then people found Entity Framework very slow…

This is the main issue of lazy loading.

Note that it is not specific to Entity Framework.


Now there is another problem with lazy loading: we could load a lot of useless data.

For example, imagine this code:

foreach (var c in _context.Customers)
    int ordersCount = c.Orders.Count;
    // Do something

With Entity Framework, when you use c.Orders, EF loads all the customer’s order if the navigation property is not already loaded.

So it means that, instead of just getting an int (the count) from the DB, EF loads all orders in memory and, only then, calculates the count.


In addition, lazy loading could not be asynchronous which also is an issue IMO.


Based on my catastrophic experience on most of developers lazy loading usage, I decided to not implement lazy loading in WAQS and to use and explicit way to load navigation properties instead.

Navigation properties loading with WAQS

However, it could be very useful to not have to know the context to load navigation properties.

In WAQS, specific extension methods on entities allow you to explicitly load navigation properties:

var order = await _context.Orders.AsAsyncQueryable().First().ExecuteAsync();
await order.LoadCustomerAsync();
await order.LoadOrderDetailsAsync();

With this way, developers know what they do and they don’t generate SQL queries without knowing it!


Note that you could parallelize the two sub queries like this:

var order = await _context.Orders.AsAsyncQueryable().First().ExecuteAsync();

var customerTask = order.LoadCustomerAsync();

await order.LoadOrderDetailsAsync();

await customerTask;

But be very careful with queries parallelization, it could have an impact on scalability.


Now what about the Count?

In order to simplify querying, you can use AsAsyncQueryable on navigation properties with many multiplicity.

So you can do it like this:

int customerOrdersCount = await customer.Orders.AsAsyncQueryable().Count().ExecuteAsync();



Without LazyLoading, you can have a risk of NullReferenceException even if the navigation property multiplicity is one (not ZeroOne).

To avoid NullReferenceException on calculated property calculation, WAQS tests null for you.

So, from GetCustomerName method we wrote in this post, WAQS generates the following code:

public string CustomerName
        if (Specifications != null && Specifications.HasCustomerName)
            return Specifications.CustomerName;
        if (this.Customer == null)
            return default (string);         return this.Customer.CustomerName;
        throw new System.InvalidOperationException();
This entry was posted in 12253, 16868, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *