ADO.NET Entity Framework Data Loading – Part 2

In my last post we have discussed about how to explicitly load data into related objects using EntityReference class.However in .NET Framework 4.0 there is another new way to explicitly load data into related objects using the LoadProperty method of the System.Data.Objects.ObjectContext as shown below:

int courseId = 2021; 

using (SchoolEntities sc = new SchoolEntities())
{
    var course = (from c in sc.Courses
                  where c.CourseID == courseId
                  select c).First();
    sc.LoadProperty<Course>(course, c => c.Department);
    Console.WriteLine("Course Name:{0}", course.Title);
    Console.WriteLine("Department Name:{0}", course.Department.Name); 

} 

Here we have used the LoadProperty method with the following signature:

public void LoadProperty<TEntity>(TEntity entity,Expression<Func<TEntity, Object>> selector) where

• TEntity is the entity class whose related objects needs to be loaded, Course in this example
• entity – instance of the entity class
• selector – A lambda expression with TEntity as input and instance of object to be loaded as output.This expression is used to select the object to be loaded.

As opposed to explicit loading there is an option of implicit Lazy Loading (new in .NET 4.0) in which data is loaded into the related objects when any properties of those objects are accessed.For this the property, LazyLoadingEnabled of ContextOptions needs to be set to true as shown below:

int courseId = 2021; 

using (SchoolEntities sc = new SchoolEntities())
{
    sc.ContextOptions.LazyLoadingEnabled = true;
    var course = (from c in sc.Courses
                  where c.CourseID == courseId
                  select c).First();
    Console.WriteLine("Course Name:{0}", course.Title);
    Console.WriteLine("Department Name:{0}", course.Department.Name); 

} 

Lazy Loading in general will result in multiple SQL queries fired to fetch data from individual tables.In some cases this might result in poor performance.So there is an option of Eager Loading as well.This can be done by using Include method of the System.Data.Objects.ObjectQuery class.The include method accepts as parameter the object path i.e path to the related object.Here multiple classes can be specified separated by dot.

int courseId = 2021; 

           using (SchoolEntities sc = new SchoolEntities())
           {
               sc.ContextOptions.LazyLoadingEnabled = false;
               var course = (from c in sc.Courses.Include("Department")
                             where c.CourseID == courseId
                             select c).First();
               Console.WriteLine("Course Name:{0}", course.Title);
               Console.WriteLine("Department Name:{0}", course.Department.Name); 

} 

In the SQL trace we can see the following query getting executed which fetches all the data for Course and Department together:

exec sp_executesql N'SELECT 
[Limit1].[CourseID] AS [CourseID], 
[Limit1].[Title] AS [Title], 
[Limit1].[Credits] AS [Credits], 
[Limit1].[DepartmentID] AS [DepartmentID], 
[Limit1].[DepartmentID1] AS [DepartmentID1], 
[Limit1].[Name] AS [Name], 
[Limit1].[Budget] AS [Budget], 
[Limit1].[StartDate] AS [StartDate], 
[Limit1].[Administrator] AS [Administrator]
FROM ( SELECT TOP (1) 
    [Extent1].[CourseID] AS [CourseID], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[Credits] AS [Credits], 
    [Extent1].[DepartmentID] AS [DepartmentID], 
    [Extent2].[DepartmentID] AS [DepartmentID1], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[Budget] AS [Budget], 
    [Extent2].[StartDate] AS [StartDate], 
    [Extent2].[Administrator] AS [Administrator]
    FROM  [dbo].[Course] AS [Extent1]
    INNER JOIN [dbo].[Department] AS [Extent2] ON [Extent1].[DepartmentID] = [Extent2].[DepartmentID]
    WHERE [Extent1].[CourseID] = @p__linq__0
)  AS [Limit1]',N'@p__linq__0 int',@p__linq__0=2021