Entity Framework lazy loading vs explicit loading and the generated SQL

Download the source code here.

If you are using lazy loading with Entity Framework you should check that queries to the database are working as expected.

A lazy load call to something like customer.Orders.FirstOrDefault() will give you a single order, but if you look at the SQL executed you’ll see that all orders for the customer are returned from the database; it is your code that is taking first one from memory. Depending on your application, this may have significant performance implications especially as the amount of data grows.

However, using explicit loading I am able to return just the one order I need.

In this post I’ll show how to use lazy loading and explicit loading, and compare the generated SQL for each.

The CustomerController contains a lazy and explicit Index action method and the OrderController contains an lazy and explicit Details action method.

To view the SQL generated for each query I used glimpse; turn it on by going to http://localhost:63817/glimpse.axd and clicking the “On” button.

Lazy Loading

By default lazy loading is enabled in EF. To take advantage of it all you do is mark the related entity as virtual, EF will do the rest.

1public class Customer
2{
3   public int CustomerID { get; set; }
4   public string Firstname { get; set; }
5   public string Lastname { get; set; }
6   public virtual ICollection<Order> Orders { get; set; }
7}

When you call customer.Orders EF overrides the Orders virtual method and makes the appropriate call to the database to load all orders for the that customer. The problem is that all orders are loaded even if you only want to the first order.

Open the attached solution and take a look a the Customer IndexLazy action method and its associated view, it shows just the first order for each customer.

The IndexLazy action method loads the customers from the database, but does not load the orders. It is in the view that the orders are lazily loaded when generating the action links to the order details page -

1item.Orders.FirstOrDefault().OrderID

Examination of the executed SQL shows that all orders for that customer were returned from the database even though only the first was used.

Explicit Loading

A more efficient approach is shown in the IndexExplicit action method. It disables lazy loading by calling -

1db.Configuration.LazyLoadingEnabled = false;

The action method then loads the customers from the database as above. Then the first order for each customer is loaded into the Orders property of the Customer.

1   foreach (var customer in customers)
2   {
3      db.Entry(customer).Collection(c => c.Orders).Query().Take(1).Load();
4   }

In this way only one order record for each customer is returned from the database.

The Data

The data model is very simple, an orders table, a customer table and an order items table. Zero or more order items are associated with an order, and zero or more orders are associated with a customer.

In the provided data, there are two customers, each with four orders made up of varying numbers of order items; take a look at the global.asax.cs to see exactly - note that in this code the way I initialize and seed the database are NOT what you should do for a serious piece of code.

Scroll to the bottom of the page to see the actual data in the tables.

The Execution

Customer page

Firstly go to the customer index which is lazily loaded - http://localhost:63817/Customer/IndexLazy

You’ll see two rows on the page representing the two customers and one order from each. Here is the sql that was executed using lazy loading.

1SELECT
2[Extent1].[CustomerID] AS [CustomerID],
3[Extent1].[Firstname] AS [Firstname],
4[Extent1].[Lastname] AS [Lastname]
5FROM [dbo].[Customers] AS [Extent1]

Two customers returned

1SELECT
2[Extent1].[OrderID] AS [OrderID],
3[Extent1].[OrderDate] AS [OrderDate],
4[Extent1].[Comment] AS [Comment],
5[Extent1].[CustomerID] AS [CustomerID]
6FROM [dbo].[Orders] AS [Extent1]
7WHERE [Extent1].[CustomerID] = 1 /*  @EntityKeyValue1 * /

All four orders for customer 1 are returned from the database even though only one is needed

1SELECT
2[Extent1].[OrderID] AS [OrderID],
3[Extent1].[OrderDate] AS [OrderDate],
4[Extent1].[Comment] AS [Comment],
5[Extent1].[CustomerID] AS [CustomerID]
6FROM [dbo].[Orders] AS [Extent1]
7WHERE [Extent1].[CustomerID] = 2 /*  @EntityKeyValue1 * /

All four orders for customer 2 are returned from the database even though only one is needed

Now go to localhost:63817/Customer/IndexExplicit and see the improvements of explicit loading.

1SELECT
2[Extent1].[CustomerID] AS [CustomerID],
3[Extent1].[Firstname] AS [Firstname],
4[Extent1].[Lastname] AS [Lastname]
5FROM [dbo].[Customers] AS [Extent1]

Two customers returned

1SELECT TOP (1)
2[Extent1].[OrderID] AS [OrderID],
3[Extent1].[OrderDate] AS [OrderDate],
4[Extent1].[Comment] AS [Comment],
5[Extent1].[CustomerID] AS [CustomerID]
6FROM [dbo].[Orders] AS [Extent1]
7WHERE [Extent1].[CustomerID] = 1 /*  @EntityKeyValue1 * /

One order returned for customer 1, this is what we wanted, note the use of TOP (1) ensuring that just one order is retrieved. Compare that to the SQL called for the lazy load.

1SELECT TOP (1)
2[Extent1].[OrderID] AS [OrderID],
3[Extent1].[OrderDate] AS [OrderDate],
4[Extent1].[Comment] AS [Comment],
5[Extent1].[CustomerID] AS [CustomerID]
6FROM [dbo].[Orders] AS [Extent1]
7WHERE [Extent1].[CustomerID] = 2 /*  @EntityKeyValue1 * /

One order returned for customer 2, again here we are getting just the one record we wanted.

Summary of lazy loading vs explicit loading for index page

When using EF it is not enough to be aware of the reasons for using iqueryable vs ienumerable, you should look at the usage scenario of the entities you are loading from the database, especially those that are lazily loaded.

Order details page

From the customer index view, navigate to the order details view for both of the customers, it shows the customer, his first order and the first order item on that first order. The order details view can be loaded from two action methods, one uses lazy loading and the other explicit.

Below are the SQL calls using lazy loading.

1SELECT TOP (2)
2[Extent1].[CustomerID] AS [CustomerID],
3[Extent1].[Firstname] AS [Firstname],
4[Extent1].[Lastname] AS [Lastname]
5FROM [dbo].[Customers] AS [Extent1]
6WHERE [Extent1].[CustomerID] = 1 /*  @p0 * /

returns one customer record

1SELECT
2[Extent1].[OrderID] AS [OrderID],
3[Extent1].[OrderDate] AS [OrderDate],
4[Extent1].[Comment] AS [Comment],
5[Extent1].[CustomerID] AS [CustomerID]
6FROM [dbo].[Orders] AS [Extent1]
7WHERE [Extent1].[CustomerID] = 1 /*  @EntityKeyValue1 * /

returns four order records even though only one is needed

1SELECT
2[Extent1].[OrderItemID] AS [OrderItemID],
3[Extent1].[ItemDescription] AS [ItemDescription],
4[Extent1].[Price] AS [Price],
5[Extent1].[OrderID] AS [OrderID]
6FROM [dbo].[OrderItems] AS [Extent1]
7WHERE [Extent1].[OrderID] = 1 /*  @EntityKeyValue1 * /

returns six order item records even though only one is needed

Here are the SQL calls explicit loading.

1SELECT TOP (2)
2[Extent1].[CustomerID] AS [CustomerID],
3[Extent1].[Firstname] AS [Firstname],
4[Extent1].[Lastname] AS [Lastname]
5FROM [dbo].[Customers] AS [Extent1]
6WHERE [Extent1].[CustomerID] = 2 /*  @p0 * /

returns just one customer

1SELECT TOP (1)
2[Extent1].[OrderID] AS [OrderID],
3[Extent1].[OrderDate] AS [OrderDate],
4[Extent1].[Comment] AS [Comment],
5[Extent1].[CustomerID] AS [CustomerID]
6FROM [dbo].[Orders] AS [Extent1]
7WHERE [Extent1].[CustomerID] = 2 /*  @EntityKeyValue1 * /

returns just one order

1SELECT TOP (1)
2[Extent1].[OrderItemID] AS [OrderItemID],
3[Extent1].[ItemDescription] AS [ItemDescription],
4[Extent1].[Price] AS [Price],
5[Extent1].[OrderID] AS [OrderID]
6FROM [dbo].[OrderItems] AS [Extent1]
7WHERE [Extent1].[OrderID] = 5 /*  @EntityKeyValue1 * /

returns just one order item

Summary of lazy loading vs explicit loading for order details page

Full Data

Customers table

Orders table

Order Item table

Download the source code here.