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.

image

Just to make it simple below is the nested structure .

image

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

image

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.

image

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) .[EmployeeNo]          AS [EmployeeNo],
               .[FirstName]           AS [FirstName],
               .[LastName]            AS [LastName],
               .[Age]                 AS [Age],
               .[DepartmentId]        AS [DepartmentId],
               .[FunctionId]          AS [FunctionId],
               .[TypeOfEmployee]      AS [TypeOfEmployee],
               .[Project_ProjectCode] AS [Project_ProjectCode]
FROM   [dbo].[Employees] AS 

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.

 

image

Lets execute the next statement.Now we see that just accessing the EmpDepartment  property has loaded the property in employee object.

image

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.

Recommended Books on Amazon :

Programming Entity Framework: Code First

Entity Framework 6 Recipes

Entity Framework Code First :The Ultimate Training
Guide

Tagged on: , ,

One thought on “Loading nested entities in EntityFramework

  1. Tero

    Very interesting article, but what if I need collections inside collection.
    Forexample I have:
    Product->Attributes(List)->Characteristics->CharacteristicOptionsSelected

    ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.