Lazy Loading And Eager Loading In LINQ To SQL

In this article, we will see

• What is Lazy Loading in LINQ to SQL?
• What is Eager Loading in LINQ to SQL?
• Difference between Page Loading and Easy Loading.

Let’s start

 

What is Lazy Loading?

 

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

 

 

 

There is one-to-many relationship between Department and Employees entities. A Department can have 1 or more employees. When the departments are loaded, the related entities (Employee entities) are not loaded. Employee entities are only loaded, when we iterate through the objects belonging to a given department.

 

Let’s prove with an example that we will create a console Application in a way, where we have the respective department and employees. Kindly refer my previous example for creation of tables (Employees and departments ).

 

Now, we have two tables here, which are Employees and Departments. Create a new blank Console Application and name it as ConsoleDemo. In this project, add a new file LINQ to SQL file and name it as sample.dbml. Afterwards, drag and drop the two tables Employees and Departments on Sample.dbml file. Subsequently, go to program.cs file and create an instance of sampledbcontext. 

class Program {
    static void Main(string[] args) {
        using(SampleDataContext context = new SampleDataContext()) {}
    }
}

Now, let’s use for each loop, loop through each department and then print the department name.

using(SampleDataContext context = new SampleDataContext()) {
    foreach(Department dept in context.Departments) {
        Console.WriteLine(dept.Name);
    }
}

Now, we also want to log the SQL query, which is generated on the console screen and to do this, we are going to make use of LOG property of the data context object.

static void Main(string[] args) {
    using(SampleDataContext context = new SampleDataContext()) {
        context.Log = Console.Out;
        foreach(Department dept in context.Departments) {
            Console.WriteLine(dept.Name);
        }
    }
}

Let’s run the Application and see the output.

 

 

 

Notice, we have all the department name and there is only one query. We don’t have any other query to load the related entities, which is Employee entity.

 

Now, in the code, we are not looping the employees, which belongs to the respective Department due to which the related entities are not loaded. Now, let’s loop through the Employee in each department. We will use another for each loop here.

foreach(Department dept in context.Departments) {
    Console.WriteLine(dept.Name);
    foreach(Employee emp in dept.Employees) {
        Console.WriteLine("\t" + emp.FirstName + " " + emp.LastName);
    }
}

Now, let's run the app and see the output.

 

 

 

Look at the output shown above, where we have one query for Department and if we try to iterate through each department, we have employees belonging to the department, which is possible when another query issues all the related employees entities, which are loaded.

 

Look at the value for the parameter for 1^st Department is 1, for HR department; it is 2 and so on. Thus, we have one query to load the Departments and three more queries to load the employees belonging to that department. Thus, it is Lazy Loading. 

 

When we need the entities, it is possible when only these entities are loaded on demand. Now, let’s look at another example, which is a Web Application. Here, we will do data binding. When we bind the data to the related entities, they will be loaded, else they won’t be loaded .

 

Thus, create a Web Application (Empty ) name its as Demo. Add a LINQ to SQL file and name it as Sample.dbml. Drag and drop the tables on the designer surface. Add a Webform and on the Webform, we need a Gridview control.

 

Code for Webform1.aspx

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

Thus, we had written columns, which we want to display, followed by boundfield control and specify the header text. We had added another template field. Here, a template field will have Itemtemplate.

 

In Itemtemplate, we had specified another GridView in which we will be displaying the employees. Specify the columns.

 

Now, let’s go to the code at the back-end file. Thus, within the page, load an event and create an instance of SampleDatacontext 

protected void Page_Load(object sender, EventArgs e) {
    using(SampleDataContext dbContext = new SampleDataContext()) {
        dbContext.Log = Response.Output;
        gvDepartments.DataSource = dbContext.Departments;
        gvDepartments.DataBind();
    }
}

Now, lets run the form and see the output.

 

 

 

As we had used dblog properties, we can see the queries on the page. Now, we will see what Eager Loading in LINQ to SQL is?

 

What is Eager Loading?

 

Eager Loading is the process where a query for one type of entity also loads related entities as a part of the query.

 

 

 

In LINQ to SQL there are two ways in which we can do Eager Loading.

• Using DataLoadOptions
• Using Projection

First, we will see how to use DataLoadOptions in Eager Loading. We will work with the same console Application.

 

Now, the data is lazily loaded.

 

 

 

First, the Departments data is loaded and when we iterate through the Employee entities belonging to the given department that is when a subsequent query is issued to load the related Employee entities. Hence, this is Lazy loading. Let’s see how to convert Lazy loading into Eager Loading, using DataLoad options.

 

DataLoad options is present in the command given below.

System.Data.Linq namespace.

Create an instance DataLoadOptions class and this Loadoptions class has loadwith method, as shown below.

using(SampleDataContext context = new SampleDataContext()) {
    context.Log = Console.Out;
    DataLoadOptions Load = new DataLoadOptions();
    Load.LoadWith < Department > (d => d.Employees);
    foreach(Department dept in context.Departments) {
        Console.WriteLine(dept.Name);
        foreach(Employee emp in dept.Employees) {
            Console.WriteLine("\t" + emp.FirstName + " " + emp.LastName);
        }
    }
}

Basically, we are telling along with department entites. Also, load the related Employee entities.

 

Finally, we need to associate these Loadoptions with the datacontext object.

context.Log = Console.Out;
DataLoadOptions Load = new DataLoadOptions();
Load.LoadWith < Department > (d => d.Employees);
context.LoadOptions = Load;

Now, lets run the app and see the output.

 

 

 

Instead of four queries, we have only one query now. This one query is going to load the Department and the related Employees data. Notice, there is a Left outer join between Employees and Departments table.

 

Let’s apply the same logic to our Web application as well.

 

 

 

One way is to use DataLoad option. Other way is to use projection. Let’s flip to Console Application.

 

Create a variable called as Linq and we will project the data in an anonymous type, as shown below.

 

var linq = from dept in context.Departments

select new { };

This anonymous type is going to have a name property and the value of the name is going to be department object. We will have another property called as Employees and this property will equal the employees from the department object.

var linq = from dept in context.Departments
select new {
    Name = dept.Name, Employees = dept.Employees
};

Lets loop through the type, as shown below.

var linq = from dept in context.Departments
select new {
    Name = dept.Name, Employees = dept.Employees
};
foreach(var dept in linq) {
    Console.WriteLine(dept.Name);
    foreach(Employee emp in dept.Employees) {
        Console.WriteLine("\t" + emp.FirstName + " " + emp.LastName);
    }
}

Let’s run the app and see the output, as shown below.

 

 

 

Notice, we only have one query to load the Employee and department data. Apply the same logic to the Web Application, where we will get the same output.

 

Now, let’s see the difference between Lazy Loading and Eager Loading

 

With Lazy Loading, there is a problem called n + 1 select problem. Let us understand this problem with an example. In this example, there is a One-to-Many relationship between Department and Employee entities. A Department can have 1 or more employees.

 

Now, let's say that we need to iterate through all the Departments and for each Department, we want to print the list of the employees. By default, LINQ to SQL would do, as shown below.

Select * from Departments
/* For each Department */
SELECT * FROM Employees WHERE DepartmentId = X

Thus, it means that we have one select for the Departments, and then N additional selects to retrieve the employees belonging to each Department, where N is the total number of Departments. Hence, this is N + 1 problem.

 

Difference between Eager Loading and Lazy Loading?

 

Which is good - Eager Loading or Lazy Loading?

 

Without looking at the Application architecture and what we are trying to achieve, we cannot say one is better over the other. Both have their own advantages and disadvantages. There are clear performance trade-offs between Eager and Lazy Loading objects from a database.

 

With Eager Loading, all the data is retrieved in a single query, which can then be cached to improve the Application performance. With Eager Loading, we are trading memory consumption for the database round trips.

 

With Lazy Loading, we only retrieve just the amount of data, which we need in a single query. When we need more data related to the initial data, additional queries are issued to the database. This means there are several round trips between the Application Server and the database Server. In general, these database round trips are very often the major performance bottleneck in most Applications. Lesser the round trips, better will be the performance.

 

For example, if on a given page, you are only displaying Departments, then there is no reason for Eager Loading related Employees data. Hence, in this case, Lazy Loading works best. On the other hand, if you are displaying both Department and Employees data, then Eager Loading works best, as it avoids the additional round trips to the database.

 

If you are not sure of what data is exactly needed, start with Lazy Loading and if it is leading to N + 1 problem then Eager Loading handles the data better.