Eager loading is a technique where EF loads the related entities along with the main entity. All entities are loaded in a single query to database thus saving bandwidth and crucial server CPU time. This is done using the Include method, which has two overloads. One of which takes navigation property as a string. The Other Include method is an extension method and far more flexible. In this tutorial, we learn how to make load the entities eagerly. We also show how to Eager Loading from multiple Levels and multiple Tables.
Table of Contents
Dangers of lazy loading
The following query retrieves list of all products from the database. In the for loop we access the p.ProductModel.Name property, which comes from the related navigational property ProductModel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var product = (from p in db.Products select p).ToList(); foreach (var p in product) { Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); } Console.ReadLine(); } |
Consider you have 100 Products in your Product table. When you iterate through over the Products collection and access the ProductModel, the EF will fire a query to retrieve the details of the ProductModel. That means EF sends 100 queries for each of those100 Products which is very inefficient.
We can solve the above problem by loading the ProductModel eagerly. along with the Products.using a Single Query.
Eager Loading in Entity Framework
One of the ways we can load entities eagerly by using the Include method. Entity Framework creates a join query, when it sees the Include method, thus bringing all the records in one single query.
The code below demonstrates the use of Include method. Disable Lazy loading before trying out any of the codes. You should also log database command to console to see the SQL command that Entity Framework sends to the database.
There are two versions of the Include method available.
The default method where you need to specify the navigational Property as a string
The other one from the System.Data.Entity namespace. It takes a lambda expression, where you need to specify the navigational Property
Include default method
The following example uses the default method. As you can see we use .Include("ProductModel") on the Products model. The ProductModel is the name of the Navigational Property (not the name of the entity).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; //Log SQL Command to Console db.Database.Log = Console.Write; var product = (from p in db.Products .Include("ProductModel") //ProductModel table to be included in the result where p.ProductID == 814 select p).ToList(); foreach (var p in product) { Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); } } |
The Corresponding SQL Query of the above method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | SELECT [Extent1].[ProductID] AS [ProductID], [Extent1].[Name] AS [Name], [Extent1].[ProductNumber] AS [ProductNumber], [Extent1].[MakeFlag] AS [MakeFlag], [Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Extent1].[Color] AS [Color], [Extent1].[SafetyStockLevel] AS [SafetyStockLevel], [Extent1].[ReorderPoint] AS [ReorderPoint], [Extent1].[StandardCost] AS [StandardCost], [Extent1].[ListPrice] AS [ListPrice], [Extent1].[Size] AS [Size], [Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Extent1].[Weight] AS [Weight], [Extent1].[DaysToManufacture] AS [DaysToManufacture], [Extent1].[ProductLine] AS [ProductLine], [Extent1].[Class] AS [Class], [Extent1].[Style] AS [Style], [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Extent1].[ProductModelID] AS [ProductModelID], [Extent1].[SellStartDate] AS [SellStartDate], [Extent1].[SellEndDate] AS [SellEndDate], [Extent1].[DiscontinuedDate] AS [DiscontinuedDate], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ProductModelID] AS [ProductModelID1], [Extent2].[Name] AS [Name1], [Extent2].[CatalogDescription] AS [CatalogDescription], [Extent2].[Instructions] AS [Instructions], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1] FROM [Production].[Product] AS [Extent1] LEFT OUTER JOIN [Production].[ProductModel] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID] WHERE 814 = [Extent1].[ProductID] |
Include Lambda Method
The Include method above takes the string as the input parameter. This can be little inconvenient as no compile-time errors are thrown in case any spelling mistakes. Hence it is advisable to use the Include extension method from the namespace System.Data.Entity
1 2 3 4 5 6 7 8 9 | using System.Data.Entity; //import it first var product = (from p in db.Products .Include(p => p.ProductModel) // Using Lambda Expression instead of a string where p.ProductID == 814 select p).ToList(); |
The method IncludeThen is introduced in Entity Framework Core, It is not available in Entity Framework 6
Eager Loading from Multiple Tables
You can load multiple related tables by using the following syntax. In the code below we have two tables ProductModel and ProductSubcategory
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var product = (from p in db.Products .Include(p => p.ProductModel) .Include(p => p.ProductSubcategory) where p.ProductID == 931 select p).ToList(); foreach (var p in product) { Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name, p.ProductSubcategory.Name) ; } } |
The corresponding SQL Query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | SELECT [Extent1].[ProductID] AS [ProductID], [Extent1].[Name] AS [Name], [Extent1].[ProductNumber] AS [ProductNumber], [Extent1].[MakeFlag] AS [MakeFlag], [Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Extent1].[Color] AS [Color], [Extent1].[SafetyStockLevel] AS [SafetyStockLevel], [Extent1].[ReorderPoint] AS [ReorderPoint], [Extent1].[StandardCost] AS [StandardCost], [Extent1].[ListPrice] AS [ListPrice], [Extent1].[Size] AS [Size], [Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Extent1].[Weight] AS [Weight], [Extent1].[DaysToManufacture] AS [DaysToManufacture], [Extent1].[ProductLine] AS [ProductLine], [Extent1].[Class] AS [Class], [Extent1].[Style] AS [Style], [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Extent1].[ProductModelID] AS [ProductModelID], [Extent1].[SellStartDate] AS [SellStartDate], [Extent1].[SellEndDate] AS [SellEndDate], [Extent1].[DiscontinuedDate] AS [DiscontinuedDate], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ProductModelID] AS [ProductModelID1], [Extent2].[Name] AS [Name1], [Extent2].[CatalogDescription] AS [CatalogDescription], [Extent2].[Instructions] AS [Instructions], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], [Extent3].[ProductSubcategoryID] AS [ProductSubcategoryID1], [Extent3].[ProductCategoryID] AS [ProductCategoryID], [Extent3].[Name] AS [Name2], [Extent3].[rowguid] AS [rowguid2], [Extent3].[ModifiedDate] AS [ModifiedDate2] FROM [Production].[Product] AS [Extent1] LEFT OUTER JOIN [Production].[ProductModel] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID] LEFT OUTER JOIN [Production].[ProductSubcategory] AS [Extent3] ON [Extent1].[ProductSubcategoryID] = [Extent3].[ProductSubcategoryID] WHERE 931 = [Extent1].[ProductID] |
Eager Loading Multiple Levels
You can also load entities to multiple levels using the following syntax. The include statement includes bothEmployee and Person table (note that both are reference types). The Query will use join to bring data from both the tables as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var person = (from p in db.SalesPersons .Include(p => p.Employee.Person) select p).Take(5).ToList(); foreach (var p in person) { Console.WriteLine("{0}", p.Employee.Person.FirstName); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | SELECT TOP (5) [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[TerritoryID] AS [TerritoryID], [Extent1].[SalesQuota] AS [SalesQuota], [Extent1].[Bonus] AS [Bonus], [Extent1].[CommissionPct] AS [CommissionPct], [Extent1].[SalesYTD] AS [SalesYTD], [Extent1].[SalesLastYear] AS [SalesLastYear], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[BusinessEntityID] AS [BusinessEntityID1], [Extent2].[NationalIDNumber] AS [NationalIDNumber], [Extent2].[LoginID] AS [LoginID], [Extent2].[OrganizationLevel] AS [OrganizationLevel], [Extent2].[JobTitle] AS [JobTitle], [Extent2].[BirthDate] AS [BirthDate], [Extent2].[MaritalStatus] AS [MaritalStatus], [Extent2].[Gender] AS [Gender], [Extent2].[HireDate] AS [HireDate], [Extent2].[SalariedFlag] AS [SalariedFlag], [Extent2].[VacationHours] AS [VacationHours], [Extent2].[SickLeaveHours] AS [SickLeaveHours], [Extent2].[CurrentFlag] AS [CurrentFlag], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], [Join2].[BusinessEntityID1] AS [BusinessEntityID2], [Join2].[PersonType] AS [PersonType], [Join2].[NameStyle] AS [NameStyle], [Join2].[Title] AS [Title], [Join2].[FirstName] AS [FirstName], [Join2].[MiddleName] AS [MiddleName], [Join2].[LastName] AS [LastName], [Join2].[Suffix] AS [Suffix], [Join2].[EmailPromotion] AS [EmailPromotion], [Join2].[AdditionalContactInfo] AS [AdditionalContactInfo], [Join2].[Demographics] AS [Demographics], [Join2].[rowguid1] AS [rowguid2], [Join2].[ModifiedDate1] AS [ModifiedDate2] FROM [Sales].[SalesPerson] AS [Extent1] INNER JOIN [HumanResources].[Employee] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] LEFT OUTER JOIN (SELECT [Extent3].[BusinessEntityID] AS [BusinessEntityID1], [Extent3].[PersonType] AS [PersonType], [Extent3].[NameStyle] AS [NameStyle], [Extent3].[Title] AS [Title], [Extent3].[FirstName] AS [FirstName], [Extent3].[MiddleName] AS [MiddleName], [Extent3].[LastName] AS [LastName], [Extent3].[Suffix] AS [Suffix], [Extent3].[EmailPromotion] AS [EmailPromotion], [Extent3].[AdditionalContactInfo] AS [AdditionalContactInfo], [Extent3].[Demographics] AS [Demographics], [Extent3].[rowguid] AS [rowguid1], [Extent3].[ModifiedDate] AS [ModifiedDate1], [Extent4].[BusinessEntityID] AS [BusinessEntityID2] FROM [Person].[Person] AS [Extent3] LEFT OUTER JOIN [HumanResources].[Employee] AS [Extent4] ON [Extent3].[BusinessEntityID] = [Extent4].[BusinessEntityID] ) AS [Join2] ON [Extent1].[BusinessEntityID] = [Join2].[BusinessEntityID2] |
The following example Queries ProductVendors which is a collection along with the Product. To get the Vendor we can make use of the select statement. Take a look at the complex query the EF generates for this task.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var product = (from p in db.Products .Include(p => p.ProductModel) .Include(p => p.ProductVendors.Select(i=> i.Vendor)) where p.ProductID == 931 select p).ToList(); foreach (var p in product) { Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); foreach (var v in p.ProductVendors) { Console.WriteLine("{0}", v.Vendor.Name); //The Vendor name is already loaded. } } } |
SQL Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | SELECT [Project1].[ProductID] AS [ProductID], [Project1].[Name] AS [Name], [Project1].[ProductNumber] AS [ProductNumber], [Project1].[MakeFlag] AS [MakeFlag], [Project1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Project1].[Color] AS [Color], [Project1].[SafetyStockLevel] AS [SafetyStockLevel], [Project1].[ReorderPoint] AS [ReorderPoint], [Project1].[StandardCost] AS [StandardCost], [Project1].[ListPrice] AS [ListPrice], [Project1].[Size] AS [Size], [Project1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Project1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Project1].[Weight] AS [Weight], [Project1].[DaysToManufacture] AS [DaysToManufacture], [Project1].[ProductLine] AS [ProductLine], [Project1].[Class] AS [Class], [Project1].[Style] AS [Style], [Project1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Project1].[ProductModelID] AS [ProductModelID], [Project1].[SellStartDate] AS [SellStartDate], [Project1].[SellEndDate] AS [SellEndDate], [Project1].[DiscontinuedDate] AS [DiscontinuedDate], [Project1].[rowguid] AS [rowguid], [Project1].[ModifiedDate] AS [ModifiedDate], [Project1].[ProductModelID1] AS [ProductModelID1], [Project1].[Name1] AS [Name1], [Project1].[CatalogDescription] AS [CatalogDescription], [Project1].[Instructions] AS [Instructions], [Project1].[rowguid1] AS [rowguid1], [Project1].[ModifiedDate1] AS [ModifiedDate1], [Project1].[C1] AS [C1], [Project1].[ProductID1] AS [ProductID1], [Project1].[BusinessEntityID] AS [BusinessEntityID], [Project1].[AverageLeadTime] AS [AverageLeadTime], [Project1].[StandardPrice] AS [StandardPrice], [Project1].[LastReceiptCost] AS [LastReceiptCost], [Project1].[LastReceiptDate] AS [LastReceiptDate], [Project1].[MinOrderQty] AS [MinOrderQty], [Project1].[MaxOrderQty] AS [MaxOrderQty], [Project1].[OnOrderQty] AS [OnOrderQty], [Project1].[UnitMeasureCode] AS [UnitMeasureCode], [Project1].[ModifiedDate2] AS [ModifiedDate2], [Project1].[BusinessEntityID1] AS [BusinessEntityID1], [Project1].[AccountNumber] AS [AccountNumber], [Project1].[Name2] AS [Name2], [Project1].[CreditRating] AS [CreditRating], [Project1].[PreferredVendorStatus] AS [PreferredVendorStatus], [Project1].[ActiveFlag] AS [ActiveFlag], [Project1].[PurchasingWebServiceURL] AS [PurchasingWebServiceURL], [Project1].[ModifiedDate3] AS [ModifiedDate3] FROM ( SELECT [Extent1].[ProductID] AS [ProductID], [Extent1].[Name] AS [Name], [Extent1].[ProductNumber] AS [ProductNumber], [Extent1].[MakeFlag] AS [MakeFlag], [Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Extent1].[Color] AS [Color], [Extent1].[SafetyStockLevel] AS [SafetyStockLevel], [Extent1].[ReorderPoint] AS [ReorderPoint], [Extent1].[StandardCost] AS [StandardCost], [Extent1].[ListPrice] AS [ListPrice], [Extent1].[Size] AS [Size], [Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Extent1].[Weight] AS [Weight], [Extent1].[DaysToManufacture] AS [DaysToManufacture], [Extent1].[ProductLine] AS [ProductLine], [Extent1].[Class] AS [Class], [Extent1].[Style] AS [Style], [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Extent1].[ProductModelID] AS [ProductModelID], [Extent1].[SellStartDate] AS [SellStartDate], [Extent1].[SellEndDate] AS [SellEndDate], [Extent1].[DiscontinuedDate] AS [DiscontinuedDate], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ProductModelID] AS [ProductModelID1], [Extent2].[Name] AS [Name1], [Extent2].[CatalogDescription] AS [CatalogDescription], [Extent2].[Instructions] AS [Instructions], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], [Join2].[ProductID] AS [ProductID1], [Join2].[BusinessEntityID1] AS [BusinessEntityID], [Join2].[AverageLeadTime] AS [AverageLeadTime], [Join2].[StandardPrice] AS [StandardPrice], [Join2].[LastReceiptCost] AS [LastReceiptCost], [Join2].[LastReceiptDate] AS [LastReceiptDate], [Join2].[MinOrderQty] AS [MinOrderQty], [Join2].[MaxOrderQty] AS [MaxOrderQty], [Join2].[OnOrderQty] AS [OnOrderQty], [Join2].[UnitMeasureCode] AS [UnitMeasureCode], [Join2].[ModifiedDate1] AS [ModifiedDate2], [Join2].[BusinessEntityID2] AS [BusinessEntityID1], [Join2].[AccountNumber] AS [AccountNumber], [Join2].[Name] AS [Name2], [Join2].[CreditRating] AS [CreditRating], [Join2].[PreferredVendorStatus] AS [PreferredVendorStatus], [Join2].[ActiveFlag] AS [ActiveFlag], [Join2].[PurchasingWebServiceURL] AS [PurchasingWebServiceURL], [Join2].[ModifiedDate2] AS [ModifiedDate3], CASE WHEN ([Join2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [Production].[Product] AS [Extent1] LEFT OUTER JOIN [Production].[ProductModel] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID] LEFT OUTER JOIN (SELECT [Extent3].[ProductID] AS [ProductID], [Extent3].[BusinessEntityID] AS [BusinessEntityID1], [Extent3].[AverageLeadTime] AS [AverageLeadTime], [Extent3].[StandardPrice] AS [StandardPrice], [Extent3].[LastReceiptCost] AS [LastReceiptCost], [Extent3].[LastReceiptDate] AS [LastReceiptDate], [Extent3].[MinOrderQty] AS [MinOrderQty], [Extent3].[MaxOrderQty] AS [MaxOrderQty], [Extent3].[OnOrderQty] AS [OnOrderQty], [Extent3].[UnitMeasureCode] AS [UnitMeasureCode], [Extent3].[ModifiedDate] AS [ModifiedDate1], [Extent4].[BusinessEntityID] AS [BusinessEntityID2], [Extent4].[AccountNumber] AS [AccountNumber], [Extent4].[Name] AS [Name], [Extent4].[CreditRating] AS [CreditRating], [Extent4].[PreferredVendorStatus] AS [PreferredVendorStatus], [Extent4].[ActiveFlag] AS [ActiveFlag], [Extent4].[PurchasingWebServiceURL] AS [PurchasingWebServiceURL], [Extent4].[ModifiedDate] AS [ModifiedDate2] FROM [Purchasing].[ProductVendor] AS [Extent3] INNER JOIN [Purchasing].[Vendor] AS [Extent4] ON [Extent3].[BusinessEntityID] = [Extent4].[BusinessEntityID] ) AS [Join2] ON [Extent1].[ProductID] = [Join2].[ProductID] WHERE 931 = [Extent1].[ProductID] ) AS [Project1] ORDER BY [Project1].[ProductID] ASC, [Project1].[ProductModelID1] ASC, [Project1].[C1] ASC |
