In this tutorial let us look into how to Load Related Entities in Entity Framework. We can load Related Entities either eagerly, lazily or explicitly. By default the EF loads the entities lazily. But you can instruct it load eagerly using the include method. You can make use of projection queries to load the data. The other option is to load the data explicitly using the load method of the context. In the explicit method, we decide when to load the data.
Table of Contents
LINQ to Entities allows you to get related entities from multiple tables using navigational properties. In Entity Framework, we use the Navigation Properties to identify each end of the association. This was explained in our tutorial Relationship in Entity Framework. There are three kinds of relationships are possible One to One relationship, One to Many relationships and Many to Many Relationship
You can refer to the following tutorial, to learn how to setup relationships in EF.
- Relationships in Entity Framework
- One to One Relationships in Entity Framework
- One to Many Relationships in Entity Framework
- Many To Many Relations in Entity Framework
We continue to use the AdventureWorks Database for our model. If you missed the previous tutorials you can go through those from the following the LINQ to Entities tutoral
There are several ways in which you can load the data from the related tables. All those methods fall into three categories
- Lazy Loading,
- Eager Loading.
- Explicit Loading
Lazy Loading
One way to retrieve the data is to load the related data, only when you actually need it. This technique is known as Lazy Loading. You can read more about Lazy Loading in Entity Framework
Example
The Product model in the AdventureWorks database has a One to Many relationship with the ProductModel model. The product belongs to only one Product model. The Product Model can have many products.
The querying for the Product is as shown below
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 | using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var products = (from p in db.Products where p.Name.StartsWith("A") & p.ProductModelID !=null select p ) .Take(5) .ToList(); foreach (var p in products) { Console.ReadLine(); Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); } } //SQL QUERY SELECT TOP (5) [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] FROM[Production].[Product] AS[Extent1] WHERE([Extent1].[Name] LIKE N'A%') AND([Extent1].[ProductModelID] IS NOT NULL) |
The code above retrieves all the Products which starts with A. Note that we have not queried for the name of the Product Model. If you look at the SQL, it is a simple Select Query.
Inside the for loop, we have accessed the ProductModel.Name. At that point, Entity Framework sends the query to the database to retrieve the ProductModel. We call this behavior as Lazy Loading in Entity Framework. The Query is as shown below. Note that EF fires this query for each iteration.
1 2 3 4 5 6 7 8 | SELECT [Extent1].[ProductModelID] AS[ProductModelID], [Extent1].[Name] AS[Name], [Extent1].[CatalogDescription] AS[CatalogDescription], [Extent1].[Instructions] AS[Instructions], [Extent1].[rowguid] AS[rowguid], [Extent1].[ModifiedDate] AS[ModifiedDate] FROM[Production].[ProductModel] AS[Extent1] WHERE[Extent1].[ProductModelID] = @EntityKeyValue1 |
Example
The Relationship between Person and email address is one to many. One person can have many email addresses. In the following example, we send the query for the Person entity.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | using (AdventureWorks db = new AdventureWorks()) { var person = (from p in db.People where p.FirstName == "KEN" select p).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3}", p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName); //Query is Fired to the data from the table foreach (var e in p.EmailAddresses) { Console.WriteLine("\t\t{0}", e.EmailAddress1); } } } |
The EF sends the following Query to the Server. Note that EF does not sends the query for the Email Address.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[PersonType] AS [PersonType], [Extent1].[NameStyle] AS [NameStyle], [Extent1].[Title] AS [Title], [Extent1].[FirstName] AS [FirstName], [Extent1].[MiddleName] AS [MiddleName], [Extent1].[LastName] AS [LastName], [Extent1].[Suffix] AS [Suffix], [Extent1].[EmailPromotion] AS [EmailPromotion], [Extent1].[AdditionalContactInfo] AS [AdditionalContactInfo], [Extent1].[Demographics] AS [Demographics], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate] FROM [Person].[Person] AS [Extent1] WHERE N'KEN' = [Extent1].[FirstName] |
In the above example retrieves all the persons with the first name KEN. We loop through each person to access their email address. Then we loop through an email address collection to get the email address of each person.
The EF sends the query for the email address as we access the email address foreach (var e in p.EmailAddresses), It only retrieves the email of one person at a time. The no of queries that EF Sends to the database is equal to the no of persons. The following shows the SQL Query that retrieves the email address.
1 2 3 4 5 6 7 8 9 10 | SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[EmailAddressID] AS [EmailAddressID], [Extent1].[EmailAddress] AS [EmailAddress], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate] FROM [Person].[EmailAddress] AS [Extent1] WHERE [Extent1].[BusinessEntityID] = @EntityKeyValue1 |
The above query in method syntax.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | //Method Syntax using (AdventureWorks db = new AdventureWorks()) { var person = db.People.Where(p => p.FirstName == "KEN").ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3}", p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName); foreach (var e in p.EmailAddresses) { Console.WriteLine("\t\t{0}", e.EmailAddress1); } } } |
Eager Loading
We can load the related entities along with the main entity. We call this technique as Eager Loading. The Lazy Loading fires a query as we access each property of the related entity. This may flood the database with several queries. In such circumstances, it is better to get all the related data ahead of time with one query to save bandwidth and crucial server resources.
Include Method
There are two versions of the Include method available. Use the Include method from the System.Data.Entity namespace. So make sure you are using that namespace.
The other one the default method where you need to specify the navigational Property as a string
We can use the Include method to load the related entities along with the main query. The include method asks the entity framework to load the related entities at the time of retrieving the main entity.
Example of Include Method
The following examples show how to use the include method to load the Entities Eagerly. Include method takes the name of the navigational Property as shown below. The email address is queried along with the main entity as shown in the SQL Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | //Query Syntax using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var person = (from p in db.People.Include(p => p.EmailAddresses) where p.FirstName == "KEN" select p).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3}", p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName); foreach (var e in p.EmailAddresses) { Console.WriteLine("\t\t{0}", e.EmailAddress1); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | //Method Syntax using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var person = db.People .Include(p => p.EmailAddresses) .Where(p => p.FirstName == "KEN").ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3}", p.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName); foreach (var e in p.EmailAddresses) { Console.WriteLine("\t\t{0}", e.EmailAddress1); } } } |
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 39 40 41 42 43 44 45 46 47 | SELECT [Project1].[BusinessEntityID] AS [BusinessEntityID], [Project1].[PersonType] AS [PersonType], [Project1].[NameStyle] AS [NameStyle], [Project1].[Title] AS [Title], [Project1].[FirstName] AS [FirstName], [Project1].[MiddleName] AS [MiddleName], [Project1].[LastName] AS [LastName], [Project1].[Suffix] AS [Suffix], [Project1].[EmailPromotion] AS [EmailPromotion], [Project1].[AdditionalContactInfo] AS [AdditionalContactInfo], [Project1].[Demographics] AS [Demographics], [Project1].[rowguid] AS [rowguid], [Project1].[ModifiedDate] AS [ModifiedDate], [Project1].[C1] AS [C1], [Project1].[BusinessEntityID1] AS [BusinessEntityID1], [Project1].[EmailAddressID] AS [EmailAddressID], [Project1].[EmailAddress] AS [EmailAddress], [Project1].[rowguid1] AS [rowguid1], [Project1].[ModifiedDate1] AS [ModifiedDate1] FROM ( SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[PersonType] AS [PersonType], [Extent1].[NameStyle] AS [NameStyle], [Extent1].[Title] AS [Title], [Extent1].[FirstName] AS [FirstName], [Extent1].[MiddleName] AS [MiddleName], [Extent1].[LastName] AS [LastName], [Extent1].[Suffix] AS [Suffix], [Extent1].[EmailPromotion] AS [EmailPromotion], [Extent1].[AdditionalContactInfo] AS [AdditionalContactInfo], [Extent1].[Demographics] AS [Demographics], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[BusinessEntityID] AS [BusinessEntityID1], [Extent2].[EmailAddressID] AS [EmailAddressID], [Extent2].[EmailAddress] AS [EmailAddress], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], CASE WHEN ([Extent2].[BusinessEntityID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [Person].[Person] AS [Extent1] LEFT OUTER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] WHERE N'KEN' = [Extent1].[FirstName] ) AS [Project1] ORDER BY [Project1].[BusinessEntityID] ASC, [Project1].[C1] ASC |
Both the above query uses the include method. Include method takes the name of the navigational Property (not the name of the related entity).
Projection
Projection queries in Entity Framework in another way to load the related data. When we use the projection Entity Framework will use the join query to get the related data.
For Example, consider this query. which queries for the Products. As you loop through the products and refer to p.ProductModel.Name the EF will send the query to the database to retrieve it. This is lazy loading.
1 2 3 4 5 6 7 8 9 10 11 12 13 | var products = (from p in db.Products where p.Name.StartsWith("A") & p.ProductModelID !=null select p ) .Take(5) .ToList(); foreach (var p in products) { Console.ReadLine(); Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); } |
But if we use the projection Query in Entity Framework to get only the columns we want (including the ProductModel) as shown below., then the EF will load the ProductModel when it retrieves the Products
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var products = (from p in db.Products where p.Name.StartsWith("A") & p.ProductModelID != null select new { p.ProductID, p.Name, p.ProductModel } ) .Take(5) .ToList(); foreach (var p in products) { Console.ReadLine(); Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); } } |
The Corresponding Query is as shown below. Note that Query retrieves all the fields of ProductModel along with the ProductID & Name of the Product.
1 2 3 4 5 6 7 8 9 10 | SELECT TOP (5) [Extent1].[ProductID] AS[ProductID], [Extent1].[Name] AS[Name], [Extent2].[ProductModelID] AS[ProductModelID], [Extent2].[Name] AS[Name1], [Extent2].[CatalogDescription] AS[CatalogDescription], [Extent2].[Instructions] AS[Instructions], [Extent2].[rowguid] AS[rowguid], [Extent2].[ModifiedDate] AS[ModifiedDate] FROM[Production].[Product] AS[Extent1] LEFT OUTER JOIN[Production].[ProductModel] AS[Extent2] ON[Extent1].[ProductModelID] = [Extent2].[ProductModelID] WHERE([Extent1].[Name] LIKE N'A%') AND([Extent1].[ProductModelID] IS NOT NULL) |
Here is another example of projection query where we use it get the collection of email addresses. Note that In the previous query a Product is associated with only one ProductModel. But in this case Person can have multiple email addresses. The EF is smart enough to know that and converts the returned data into collection of email addresses.
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 | using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var person = (from e in db.People where e.FirstName == "KEN" select new { ID = e.BusinessEntityID, FirstName = e.FirstName, MiddleName = e.MiddleName, LastName = e.LastName, EmailAddresses = e.EmailAddresses }).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3} ", p.ID, p.FirstName, p.MiddleName, p.LastName); foreach (var e in p.EmailAddresses) { Console.WriteLine("\t\t{0}", e.EmailAddress1); } } } //SQL QUERY SELECT [Project1].[BusinessEntityID] AS [BusinessEntityID], [Project1].[FirstName] AS [FirstName], [Project1].[MiddleName] AS [MiddleName], [Project1].[LastName] AS [LastName], [Project1].[C1] AS [C1], [Project1].[BusinessEntityID1] AS [BusinessEntityID1], [Project1].[EmailAddressID] AS [EmailAddressID], [Project1].[EmailAddress] AS [EmailAddress], [Project1].[rowguid] AS [rowguid], [Project1].[ModifiedDate] AS [ModifiedDate] FROM ( SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[FirstName] AS [FirstName], [Extent1].[MiddleName] AS [MiddleName], [Extent1].[LastName] AS [LastName], [Extent2].[BusinessEntityID] AS [BusinessEntityID1], [Extent2].[EmailAddressID] AS [EmailAddressID], [Extent2].[EmailAddress] AS [EmailAddress], [Extent2].[rowguid] AS [rowguid], [Extent2].[ModifiedDate] AS [ModifiedDate], CASE WHEN ([Extent2].[BusinessEntityID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [Person].[Person] AS [Extent1] LEFT OUTER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] WHERE N'KEN' = [Extent1].[FirstName] ) AS [Project1] |
Explicit Loading
Explicit Loading is another way to load the related data. Here we make a explicit call the Load method to load the related data.
First, we need to disable the Lazy Loading
db.Configuration.LazyLoadingEnabled = false;
Use the Entry method on the entity and use the Reference method and call the Load on that related entity, which you want to load. In case of collections use the collection method instead of Reference method
db.Entry(p).Reference(m => m.ProductModel).Load();
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; db.Database.Log = Console.Write; var product = (from p in db.Products orderby p.ProductID descending select p).Take(5).ToList(); foreach (var p in product) { db.Entry(p).Reference(m => m.ProductModel).Load(); //Explicit Loading Console.WriteLine("{0} {1} Product Model => {2}", p.ProductID, p.Name, (p.ProductModel == null) ? "" : p.ProductModel.Name); Console.ReadKey(); } } |
Summary
In this article, we showed you how to load the related data using various methods like lazy loading, eager loading with projection query and include method. Explicit loading with the load method.
