Loading nested entities in EntityFramework
In this post I am going to discuss various options available for loading nested objects or entities when using entity framework.Below is the class structure I’ll be working with.This post requires that you have basic knowledge of entity framework.
In case you are new to entity framework here is an excellent course on Pluralsight on getting started with entity framework.
Just to make it simple below is the nested structure .
Eager Loading
For eager loading we use “Include” linq method as shown below.
using (var context = new EmployeeContext())
{
var employees = context.Employees.Include(x => x.ContactDetails).Include(x=>x.EmpDepartment).Include(x=>x.EmpDepartment.DepartmentProjects).ToList();
};
This will load ContactDetails,Department and DepartmentProjects when loading employee entitiy but not Qualifications (as I did not use include for the same).
Running SQL Profiler below is the query that is executed when ToList() is called in the above snippet.
SELECT [Project1].[EmployeeNo1] AS [EmployeeNo],
[Project1].[EmployeeNo] AS [EmployeeNo1],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[Age] AS [Age],
[Project1].[DepartmentId] AS [DepartmentId],
[Project1].[FunctionId] AS [FunctionId],
[Project1].[TypeOfEmployee] AS [TypeOfEmployee],
[Project1].[Address] AS [Address],
[Project1].[Phone] AS [Phone],
[Project1].[Fax] AS [Fax],
[Project1].[Mobile] AS [Mobile],
[Project1].[LocationCord] AS [LocationCord],
[Project1].[DepartmentId1] AS [DepartmentId1],
[Project1].[DepartmentCode] AS [DepartmentCode],
[Project1].[DepartmentName] AS [DepartmentName],
[Project1].[Project_ProjectCode] AS [Project_ProjectCode],
[Project1].[C1] AS [C1],
[Project1].[ProjectCode] AS [ProjectCode],
[Project1].[ProjectName] AS [ProjectName],
[Project1].[ProjectDescription] AS [ProjectDescription],
[Project1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM (SELECT [Extent1].[EmployeeNo] AS [EmployeeNo],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Age] AS [Age],
[Extent1].[DepartmentId] AS [DepartmentId],
[Extent1].[FunctionId] AS [FunctionId],
[Extent1].[TypeOfEmployee] AS [TypeOfEmployee],
[Extent1].[Project_ProjectCode] AS [Project_ProjectCode],
[Extent2].[EmployeeNo] AS [EmployeeNo1],
[Extent2].[Address] AS [Address],
[Extent2].[Phone] AS [Phone],
[Extent2].[Fax] AS [Fax],
[Extent2].[Mobile] AS [Mobile],
[Extent2].[LocationCord] AS [LocationCord],
[Extent3].[DepartmentId] AS [DepartmentId1],
[Extent3].[DepartmentCode] AS [DepartmentCode],
[Extent3].[DepartmentName] AS [DepartmentName],
[Extent4].[ProjectCode] AS [ProjectCode],
[Extent4].[ProjectName] AS [ProjectName],
[Extent4].[ProjectDescription] AS [ProjectDescription],
[Extent4].[Department_DepartmentId] AS [Department_DepartmentId],
CASE
WHEN ([Extent4].[ProjectCode] IS NULL) THEN CAST(NULL AS int)
ELSE 1
END AS [C1]
FROM [dbo].[Employees] AS [Extent1]
LEFT OUTER JOIN [dbo].[EmployeeContacts] AS [Extent2]
ON [Extent1].[EmployeeNo] = [Extent2].[EmployeeNo]
INNER JOIN [dbo].[Departments] AS [Extent3]
ON [Extent1].[DepartmentId] = [Extent3].[DepartmentId]
LEFT OUTER JOIN [dbo].[Projects] AS [Extent4]
ON ([Extent4].[Department_DepartmentId] IS NOT NULL)
AND ([Extent1].[DepartmentId] = [Extent4].[Department_DepartmentId])) AS [Project1]
ORDER BY [Project1].[EmployeeNo1] ASC,
[Project1].[EmployeeNo] ASC,
[Project1].[DepartmentId1] ASC,
[Project1].[C1] ASC
Looking at the query you can see using three or more includes can really complicate the underlying query and possibly the performance as well.It is good idea to limit includes to two unless you are sure about what you are doing.
Using Projection
Next option is to use projections to load related entities i.e. we explicitly return required entities in Select clause as shown below.
using (var context = new EmployeeContext())
{
var employees = context.Employees.Select(e => new
{
e,
e.EmpDepartment,
e.ContactDetails,
e.EmpDepartment.DepartmentProjects
}).ToList();
};
Just calling explicitly EmpDepartment and DepartmentProjects in Select will load these entities in Employee object hierarchy as shown below.
Below is the underlying query from profiler.
SELECT [Project1].[EmployeeNo1] AS [EmployeeNo],
[Project1].[EmployeeNo2] AS [EmployeeNo1],
[Project1].[EmployeeNo] AS [EmployeeNo2],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[Age] AS [Age],
[Project1].[DepartmentId] AS [DepartmentId],
[Project1].[FunctionId] AS [FunctionId],
[Project1].[TypeOfEmployee] AS [TypeOfEmployee],
[Project1].[Project_ProjectCode] AS [Project_ProjectCode],
[Project1].[DepartmentId1] AS [DepartmentId1],
[Project1].[DepartmentCode] AS [DepartmentCode],
[Project1].[DepartmentName] AS [DepartmentName],
[Project1].[Address] AS [Address],
[Project1].[Phone] AS [Phone],
[Project1].[Fax] AS [Fax],
[Project1].[Mobile] AS [Mobile],
[Project1].[LocationCord] AS [LocationCord],
[Project1].[C1] AS [C1],
[Project1].[ProjectCode] AS [ProjectCode],
[Project1].[ProjectName] AS [ProjectName],
[Project1].[ProjectDescription] AS [ProjectDescription],
[Project1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM (SELECT [Extent1].[EmployeeNo] AS [EmployeeNo],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Age] AS [Age],
[Extent1].[DepartmentId] AS [DepartmentId],
[Extent1].[FunctionId] AS [FunctionId],
[Extent1].[TypeOfEmployee] AS [TypeOfEmployee],
[Extent1].[Project_ProjectCode] AS [Project_ProjectCode],
[Extent2].[EmployeeNo] AS [EmployeeNo1],
[Extent3].[DepartmentId] AS [DepartmentId1],
[Extent3].[DepartmentCode] AS [DepartmentCode],
[Extent3].[DepartmentName] AS [DepartmentName],
[Extent4].[EmployeeNo] AS [EmployeeNo2],
[Extent4].[Address] AS [Address],
[Extent4].[Phone] AS [Phone],
[Extent4].[Fax] AS [Fax],
[Extent4].[Mobile] AS [Mobile],
[Extent4].[LocationCord] AS [LocationCord],
[Extent5].[ProjectCode] AS [ProjectCode],
[Extent5].[ProjectName] AS [ProjectName],
[Extent5].[ProjectDescription] AS [ProjectDescription],
[Extent5].[Department_DepartmentId] AS [Department_DepartmentId],
CASE
WHEN ([Extent5].[ProjectCode] IS NULL) THEN CAST(NULL AS int)
ELSE 1
END AS [C1]
FROM [dbo].[Employees] AS [Extent1]
LEFT OUTER JOIN [dbo].[EmployeeContacts] AS [Extent2]
ON [Extent1].[EmployeeNo] = [Extent2].[EmployeeNo]
INNER JOIN [dbo].[Departments] AS [Extent3]
ON [Extent1].[DepartmentId] = [Extent3].[DepartmentId]
LEFT OUTER JOIN [dbo].[EmployeeContacts] AS [Extent4]
ON [Extent1].[EmployeeNo] = [Extent4].[EmployeeNo]
LEFT OUTER JOIN [dbo].[Projects] AS [Extent5]
ON ([Extent5].[Department_DepartmentId] IS NOT NULL)
AND ([Extent1].[DepartmentId] = [Extent5].[Department_DepartmentId])) AS [Project1]
ORDER BY [Project1].[EmployeeNo1] ASC,
[Project1].[EmployeeNo2] ASC,
[Project1].[EmployeeNo] ASC,
[Project1].[DepartmentId1] ASC,
[Project1].[C1] ASC
One advantage of using projections over eager loading is that you can easily perform sorting operations on nested entities in case of Projections.
Explicit Loading
Third option is to use explicit loading where you specifically call Load method as shown below on the Collection or Property which you want to load.
using (var context = new EmployeeContext())
{
var employee = context.Employees.FirstOrDefault();
context.Entry(employee).Reference(x => x.ContactDetails).Load();
context.Entry(employee).Reference(x => x.EmpDepartment).Load();
context.Entry(employee.EmpDepartment).Collection(x => x.DepartmentProjects).Load();
};
For a collection we use Collection function and for a simple navigation property we use Reference function. Well in this case as it can be understood there are four separate underlying queries that are executed corresponding to four of the above statements.
SELECT TOP (1) [c].[EmployeeNo] AS [EmployeeNo],
[c].[FirstName] AS [FirstName],
[c].[LastName] AS [LastName],
[c].[Age] AS [Age],
[c].[DepartmentId] AS [DepartmentId],
[c].[FunctionId] AS [FunctionId],
[c].[TypeOfEmployee] AS [TypeOfEmployee],
[c].[Project_ProjectCode] AS [Project_ProjectCode]
FROM [dbo].[Employees] AS [c]
SELECT [Extent1].[EmployeeNo] AS [EmployeeNo],
[Extent1].[Address] AS [Address],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax],
[Extent1].[Mobile] AS [Mobile],
[Extent1].[LocationCord] AS [LocationCord]
FROM [dbo].[EmployeeContacts] AS [Extent1]
WHERE [Extent1].[EmployeeNo] = 1 /* @EntityKeyValue1 */
SELECT [Extent1].[DepartmentId] AS [DepartmentId],
[Extent1].[DepartmentCode] AS [DepartmentCode],
[Extent1].[DepartmentName] AS [DepartmentName]
FROM [dbo].[Departments] AS [Extent1]
WHERE [Extent1].[DepartmentId] = 11 /* @EntityKeyValue1 */
SELECT [Extent1].[ProjectCode] AS [ProjectCode],
[Extent1].[ProjectName] AS [ProjectName],
[Extent1].[ProjectDescription] AS [ProjectDescription],
[Extent1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM [dbo].[Projects] AS [Extent1]
WHERE ([Extent1].[Department_DepartmentId] IS NOT NULL)
AND ([Extent1].[Department_DepartmentId] = 11 /* @EntityKeyValue1 */)
This will be helpful when you need to take full control and you know exactly when you want what entities to be loaded.This can also give you some performance boost due to simple fact that you are not loading everything together.
Lazy Loading
Fourth and last option is the one where we don’t have to do anything for loading nested entities but there are certain requirements which should be fulfilled so that we can use lazy loading.
First requirement is to enable lazy loading .
context.Configuration.LazyLoadingEnabled = true;
Second all the properties on which lazy loading need to work should be marked virtual.
public class Employee
{
[Key]
public int EmployeeNo { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public virtual EmployeeContact ContactDetails { get; set; }
public int Age { get; set; }
public int DepartmentId { get; set; }
public int FunctionId { get; set; }
public EmployeeType TypeOfEmployee { get; set; }
public virtual ICollection<Qualification> Qualifications{ get; set; }
public virtual Department EmpDepartment { get; set; }
public Function EmpFunction { get; set; }
}
In case of lazy loading whenever you access an entity is the time when query is fired and entity is loaded. Lets execute below code.
using (var context = new EmployeeContext())
{
context.Configuration.LazyLoadingEnabled = true;
var employee = context.Employees.FirstOrDefault();
var department = employee.EmpDepartment;
var qualifications = employee.Qualifications;
}
Below is the object state when var employee = context.Employees.FirstOrDefault(); is executed.We see only first level properties are loaded.
Lets execute the next statement.Now we see that just accessing the EmpDepartment property has loaded the property in employee object.
Lazy loading has an advantage that it gives you a performance boost as it loads stuff on need basis but flip side of this behaviour is that you loose control over what loads when and this can lead to problems sometimes.
5 thoughts on “Loading nested entities in EntityFramework”
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Very interesting article, but what if I need collections inside collection.
Forexample I have:
Product->Attributes(List)->Characteristics->CharacteristicOptionsSelected
?
I would also love to see an answer to Tero’s reply above if anyone can help please?
ApplicationDbContext.Entry(MyEntity).Collection(x => x.MyMainCollection)
.Query()
.Include(y=>y.MySubCollection)
.Load();
If we use Eager Loading we can use ThenInclude for multiple:
Customer customer = await dbcontext.Customers
.Include(cust => cust.pets)
.ThenInclude(pet => pet.Notes)
.FirstOrDefaultAsync(f => f.Id == id);
If using Explicit Loading we could load the entity first this:
Customer customer = await db.Customers .FirstOrDefaultAsync(f => f.Id == id);
db.Entry(customer).Collection(r => r.Pets).Load();
foreach (Pet pet in customer.Pets)
{
db.Entry(pet).Collection(c => c.HealthIssues).Load();
}
First, we loaded the customer, then we loaded the Pets for this customer, then for each pet, we loaded the Healt Issues of the pet.
Is it possible to project only few columns from the included tables ?
In your example, say I want to have only few columns from ContactDetails instead of all the columns.