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.

Download the source code here.

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.

public class Customer
{
   public int CustomerID { get; set; }
   public string Firstname { get; set; }
   public string Lastname { get; set; }
   public virtual ICollection<Order> Orders { get; set; }
}

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 –

item.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 –

db.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.

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

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.

SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname]
FROM [dbo].[Customers] AS [Extent1]

Two customers returned

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 1 /* @EntityKeyValue1 */

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

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [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.

SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname]
FROM [dbo].[Customers] AS [Extent1]

Two customers returned

SELECT TOP (1)
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [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.

SELECT TOP (1)
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [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

TaskLazy Loaded RowsExplicit Loaded Rows
Retrieve customers22
Retrieve Steve's orders41
Retrieve James' orders 41

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.

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

returns one customer record

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 1 /* @EntityKeyValue1 */

returns four order records even though only one is needed

SELECT
[Extent1].[OrderItemID] AS [OrderItemID],
[Extent1].[ItemDescription] AS [ItemDescription],
[Extent1].[Price] AS [Price],
[Extent1].[OrderID] AS [OrderID]
FROM [dbo].[OrderItems] AS [Extent1]
WHERE [Extent1].[OrderID] = 1 /* @EntityKeyValue1 */

returns six order item records even though only one is needed

Here are the SQL calls explicit loading.

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

returns just one customer

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

returns just one order

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

returns just one order item

Summary of lazy loading vs explicit loading for order details page

TaskLazy Loaded RowsExplicit Loaded Rows
Retrieve customers11
Retrieve orders41
Retrieve order items61




Full Data

Customers table

CustomerIDFirstnameLastname
1Lazy SteveSmith
2JamesJones

Orders table

OrderIDOrderDateCommentCustomerID
18/6/2014 12:00:00 AM Steve's first order1
28/7/2014 12:00:00 AMSteve's second order1
38/8/2014 12:00:00 AMSteve's third order1
48/9/2014 12:00:00 AMSteve's fourth order1
58/7/2014 12:00:00 AMJames' first order2
68/8/2014 12:00:00 AMJames' second order2
78/9/2014 12:00:00 AMJames' third order2
88/10/2014 12:00:00 AMJames' fourth order2

Order Item table

OrderItemIDItemDescriptionPriceOrderID
1Ball31
2Watch321
3Book121
4Glasses411
5Pen41
6Chair231
7Laptop4002
8Mouse132
9Bottle12
10Stapler72
11Wires2.52
12Hose413
13Tiles224
14Table495
15Spoons1.995
16Forks2.995
17Cups55
18Plates145
19Knives85
20Bike3406
21Reflectors96
22Tool106
23Tube repair4.996
24Lights116
25Helmet276
26Mouse147
27Keyboard297
28Mouse148
29Keyboard298

Download the source code here.