Part 8 - Lazy loading in LINQ to SQL

Suggested Videos
Part 5 - Insert Update Delete using stored procedures in LINQ to SQL
Part 6 - Stored procedures with output parameters in LINQ to SQL
Part 7 - What is SqlMetal



In this video we will discuss the concept of Lazy loading in LINQ to SQL with an example.



Example 1 :

Step 1 : Create a database. Name it Sample.

Step 2 : We will be using Departments and Employees tables in this demo. Create the tables using the following SQL script.

Create table Departments

(

     ID int primary key identity,

     Name nvarchar(50),

     Location nvarchar(50)

)

GO

Create table Employees

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int,

     DepartmentId int foreign key references Departments(Id)

)

GO

Insert into Departments values ('IT', 'New York')

Insert into Departments values ('HR', 'London')

Insert into Departments values ('Payroll', 'Sydney')

GO

Insert into Employees values ('Mark', 'Hastings', 'Male', 60000, 1)

Insert into Employees values ('Steve', 'Pound', 'Male', 45000, 3)

Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000, 1)

Insert into Employees values ('Philip', 'Hastings', 'Male', 45000, 2)

Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000, 2)

Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000, 3)

Insert into Employees values ('John', 'Stanmore', 'Male', 80000, 1)

GO

Step 3 : Create a new console application. Name it ConsoleDemo.

Step 4 : Right click on the project in solution explorer and add LINQ to SQL Classes. Change the name from DataClasses1.dbml to Sample.dbml

Step 5 : At this point, Sample.dbml file should have been added to the project. Click on Server Explorer link on Sample.dbml file. In the Server Explorer window, you should find all the tables in the Sample database. Drag and drop the tables on Sample.dbml file.


Step 6 : At this point we should have Department and Employee classes. The properties of the class map to the columns of the respective table in the database. The arrow between the classes represent the association between them. These associations are modeled based on the primary-key/foreign-key relationships between the tables in the database. Notice that the arrow is pointing from Department to Employee entity. In this case there is a One-to-Many relationship between Department and Employee entities. A Department can have 1 or more employees.


Step 7 : Copy and paste the following code in the Main() method in Program.cs file.

using (SampleDataContext dbContext = new SampleDataContext())

{

    //dbContext.Log = Console.Out;

    foreach (Department dept in dbContext.Departments)

    {

        Console.WriteLine(dept.Name);

        foreach (Employee emp in dept.Employees)

        {

            Console.WriteLine("\t" + emp.FirstName + " " + emp.LastName);

        }

    }

}

Step 8 : The above code prints, Department Name and all the employees FirstName and LastName's belonging to that department. The output is shown below.


In this example there is a One-to-Many relationship between Department and Employee entities. A Department can have 1 or more employees. When Departments are loaded the related entities (Employee entities) are not loaded. Employee entities are only loaded when we iterate thru the employee objects belonging to a given department.

To prove this, uncomment the following line and run the console application again. Notice that there is one query to retrieve all the departments and 3 queries to retrieve the employees belonging to each department.

//dbContext.Log = Console.Out;

Example 2 : Let us display Department Name and Employee Names in a GridView control as shown below in an asp.net web application. 


Step 1 : Create a new empty asp.net web application project. 

Step 2 : Name it Demo. Follow the steps in Example 1, from Step 4 to Step 6:

Step 3 : Add a WebForm to the project. Copy and paste the following HTML in the aspx page.

<asp:GridView ID="gvDepartments" runat="server" AutoGenerateColumns="False">

    <Columns>

        <asp:BoundField HeaderText="Department" DataField="Name" />

        <asp:TemplateField HeaderText="Employees">

            <ItemTemplate>

                <asp:GridView ID="gvEmployees" runat="server"

                                AutoGenerateColumns="false"

                                DataSource='<%# Eval("Employees") %>'>

                    <Columns>

                        <asp:BoundField DataField="FirstName" HeaderText="First Name" />

                        <asp:BoundField DataField="LastName" HeaderText="Last Name" />

                    </Columns>

                </asp:GridView>

            </ItemTemplate>

        </asp:TemplateField>

    </Columns>

</asp:GridView>

Step 4 : Copy and paste the following code in the Page_Load() event of the code-behind file.

using (SampleDataContext dbContext = new SampleDataContext())

{

    dbContext.Log = Response.Output;

    gvDepartments.DataSource = dbContext.Departments;

    gvDepartments.DataBind();

}

Run the application and notice that there is one query to retrieve all the departments and 3 queries to retrieve the employees belonging to each department. In this example we are data binding both departments and employees.

So, what is Lazy Loading
Lazy Loading means the related entities are not loaded until we iterate thru them or data bind them. By default, LINQ to SQL loads related entities by using Lazy Loading.

In our next video, we will discuss how to change this behavior and eager load the related entities.