In some cases, users may not be interested to view entire data too. Hence, loading entire data is not that great idea in these scenarios. In these cases, one can decide to lazy load the data depending upon their situations for gaining better performance, etc.

Moving forward, we will see how to lazy load data in C#.

For simplicity and easy understanding, consider an Employee details table which has all employee related data including a picture of the employee. Refer the below figure,

In this case, whenever we need to load the employee details it will be a bad idea to load employee data including the picture column in the initial data load. It is always good and efficient to load the picture only when it is required instead of loading it initially because there may be cases it may not be used. Since Picture is a binary data, loading it whenever it is required will in turn give you some performance benefits.

Let’s see how we can implement this in the Employee entity.

Lazy Loading in C#

public class Employee

{

int _empID;

string _empname = String.Empty;

int _dept;

int _age;

string _address = String.Empty;

byte[] _picture = null;

public Employee()

    {

             //

             // TODO: Add constructor logic here

             //

    }

    #region Public Members

public int EmpID

    {

get

        {

return _empID;

        }

set

        {

            _empID = value;

        }

    }

public string EmpName

    {

get

        {

return _empname;

        }

set

        {

            _empname = value;

        }

    }

public int DeptID

    {

get

        {

return _dept;

        }

set

        {

            _dept = value;

        }

    }

public int Age

    {

get

        {

return _age;

        }

set

        {

            _age = value;

        }

    }

public string Address

    {

get

        {

return _address;

        }

set

        {

            _address = value;

        }

    }

public byte[] Picture

    {

get

        {

if (_picture == null)

         _picture = GetPicture();

return _picture;

        }

set

        {

            _picture = value;

        }

    }

    #endregion

private byte[] GetPicture()

    {

byte[] pic = null;

        //get image code

return pic;

    }

}

If you see the above code, the Picture property has the capability to load the data only when it is accessed (in other words, the picture will be loaded only when it is required). So, whenever you want to load the employee details from database you can initialize all the other properties except Picture which can be lazy loaded at any time. Again, to re-iterate you can decide to do this only in cases where you have a need or when this will benefit your application, because doing like this may have adverse effect like increased the number of DB calls, etc.

Lazy loading is also adapted in .Netframework’s ORM tools LINQ to SQL and LINQ to Entities. Let’s see about this in next section.

Lazy Loading in LINQ to SQL

If you are new to LINQ to SQL, you can read the below article to have a better understanding of this section.

Introduction to LINQ to SQL – A Beginners Guide

When using LINQ to SQL, any property of our entity can be set to lazy loaded by setting its “Delay Loaded” property to true. For example, if you want to lazy load the Picture property then open your LINQ to SQL designer, right click the Picture field and select Property. Set “Delay Loaded” property to true. Refer the below figure,

Doing like above will make Picture field to be lazy loaded whenever the Employee details are loaded.

Refer the below code,

protected void Page_Load(object sender, EventArgs e)

    {

EmployeeDataClassesDataContextdatacontext = new EmployeeDataClassesDataContext();

var emps = from em in datacontext.Employees

select em;

foreach (Employee emp in emps)

        {

string name = emp.EmpName;          

        }  

    }

When you execute the above code, the LINQ to SQL will send a query without picture column in the field list of the select query to SQL server.  You can see the query below which is sent to SQL server when the above code is executed,

SELECT [t0].[EmpID], [t0].[EmpName], [t0].[DeptID], [t0].[Age], [t0].[Address]

FROM [dbo].[Employee] AS [t0]

Note

You can see the query sent to SQL server by having breakpoint on the LINQ query line and by hovering the cursor over the variable emps.

You can also see this in the SQL profiler. Refer below,

And, when you try to access the Picture property like below,

EmployeeDataClassesDataContextdatacontext = new EmployeeDataClassesDataContext();

var emps = from em in datacontext.Employees

select em;

foreach (Employee emp in emps)

        {

string name = emp.EmpName;

byte[] pic = emp.Picture.ToArray();

        } 

Now you will be able to see a separate query getting executed for lazy loading the picture.

Refer the below SQL profiler screen shot,

Immediate Load or Eager Load

At some point in time, we may also have a need to load the Picture property along with the other properties. This is called immediate load or eager load, an opposite term of lazy load.

To eagerly load or immediately load the Picture with Employee object we can use DataLoadOptions (namespace:System.Data.Linq)object to specify the framework to load Picture object along with the main object. Refer the below code,

EmployeeDataClassesDataContextdatacontext = new EmployeeDataClassesDataContext();

Var emps = from em in datacontext.Employees

Select em;

        DataLoadOptions dboption = new DataLoadOptions();

dboption.LoadWith<Employee>(lemp =>lemp.Picture);

datacontext.LoadOptions = dboption;

foreach (Employee emp in emps)

        {

string name = emp.EmpName;

byte[] pic = emp.Picture.ToArray();

        } 

You can enable the profiler to check the query sent to SQL server when executing the above code. Refer the below figure,

If you would like to know more about DataLoadOptions and Immediate load you read the below article,

Using DataLoadOptions to Fetch(Immediate Load) the Related Objects in LINQ