ADO.NET Entity Framework Data Loading – Part 1

Posted: May 9, 2010 in .NET, ADO.NET Entity Framework
Tags: ,

In any Object Relational Mapping (ORM) framework the client code interacts with the objects.The framework maps the same to relational database tables and manages database connection and all the SQL queries.So in this context it is extremely important to understand what are the various mechanisms using which data can be loaded into the objects.In this series of posts we will discuss on how ADO.NET Entity Framework does the same.To start with we will discuss about how the objects are explicitly populated with data.

I have considered the following entity relationship in my sample code:

ef

The following lines of code is used to retrieve a particular course from the database.

int courseId = 2021; 

using (SchoolEntities sc = new SchoolEntities())
{
     var course = (from c in sc.Course
                  where c.CourseID == courseId
                  select c).First(); 

     Console.WriteLine("Course Name:{0}", course.Title);

     Console.WriteLine("Department Name:{0}", course.Department.Name);
}

Now while running this code we can see that Course Name is printed on screen and then there is a NullReferenceException while printing the Department name as the Department object is null.Using SQL Profiler I found out that the following query is getting executed:

exec sp_executesql N'SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[CourseID] AS [CourseID], 
[Limit1].[Title] AS [Title], 
[Limit1].[Credits] AS [Credits], 
[Limit1].[DepartmentID] AS [DepartmentID]
FROM ( SELECT TOP (1) 
    [Extent1].[CourseID] AS [CourseID], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[Credits] AS [Credits], 
    [Extent1].[DepartmentID] AS [DepartmentID], 
    1 AS [C1]
    FROM [dbo].[Course] AS [Extent1]
    WHERE [Extent1].[CourseID] = @p__linq__1
)  AS [Limit1]',N'@p__linq__1 int',@p__linq__1=2021 

Here we can clearly see that data is fetched only for Course.So what needs to be done if we want the same to be done for Department to which the select Course belongs to.To understand this we need to take a look at how the relationship between Course and Department is modeled in EF.This is done using the following property in the Course class:

public global::System.Data.Objects.DataClasses.EntityReference<Department> DepartmentReference
{
    get
    {
        return ((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.GetRelatedReference<Department>("SchoolModel.FK_Course_Department", "Department");
    }
    set
    {
        if ((value != null))
        {
            ((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.InitializeRelatedReference<Department>("SchoolModel.FK_Course_Department", "Department", value);
        }
    }
} 

The EntityReference<TEntity> class represents the association between two entities and one of it’s base classes RelatedEnd provides a method Load which is used to load data into related objects and a property named IsLoaded which tells us whether data has been loaded into the related class.These are used to explicitly load data into related objects as shown below:

int courseId = 2021; 

using (SchoolEntities sc = new SchoolEntities())
{
    var course = (from c in sc.Course
                 where c.CourseID == courseId
                 select c).First(); 

    Console.WriteLine("Course Name:{0}", course.Title);
    if (!course.DepartmentReference.IsLoaded)
    {
        course.DepartmentReference.Load();
    }
    Console.WriteLine("Department Name:{0}", course.Department.Name); 

} 

In the SQL Trace we can see the following additional query getting fired:

exec sp_executesql N'SELECT 
[Extent2].[DepartmentID] AS [DepartmentID], 
[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] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2021 

At times this kind of approach might be tedious to code and also may lead to performance issues as individual queries needs to be fired each time we loaded data into related objects.So what are the other options.We will check them in the next post. 

About these ads
Comments
  1. ADO.NET Entity Framework Data Loading – Part 1 « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. ADO.NET Entity Framework Data Loading – Part 1 « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from PimpThisBlog.com…

  3. ADO.NET Entity Framework Data Loading – Part 1 « Sankarsan’s Journal…

    Thank you for submitting this cool story – Trackback from Servefault.com…

  4. […] this article: ADO.NET Entity Framework Data Loading – Part 1 « Sankarsan's Journal No […]

  5. […] 9, 2010 by sankarsan In my last post we have discussed about how to explicitly load data into related objects using EntityReference […]

  6. progg.ru says:

    ADO.NET Entity Framework Data Loading – Part 1…

    Thank you for submitting this cool story – Trackback from progg.ru…

  7. […] This post was mentioned on Twitter by progg, ru_webdev. ru_webdev said: ADO.NET Entity Framework Data Loading – Part 1: In any Object Relational Mapping (ORM) framework the client code i… http://bit.ly/aWxF9h […]

  8. […] ADO.NET Entity Framework Data Loading – Part 1 « Sankarsan's Journal […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s