In this post I would like to talk about LINQ to SQL and its native lazy loading functionality. I will show you how you can change this behavior. We will create a simple ASP.Net application to demonstrate this.

I have seen a lot of people struggling with performance issues when it comes to LINQ to SQL. That is mostly due to the lack of knowledge of how LINQ internally works.Imagine that we have two tables Products and Suppliers (Northwind database).

There is one to many relationship between those tables-entities. One supplier supplies many products. Let's say that we want to access a property from a related entity. We want to access a property from the Products entity through the Suppliers entity.The LINQ to SQL engine will create a query that will go and fetch the data at that very moment. This can be very bad for the performance of our application.Imagine the scenario that you want to retrieve rows of an entity and you need the related data as well. Let's say that you will return 1000 rows of the primary entity and its related data, that means another 1000 queries to the database.

So keep in mind that lazy loading is enabled by default and I will prove that with an example. Then as I said before I will show you how to change that behavior.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Suppliers and the Products tables and drop them on the Northwind.dbml.

4)  We want to demonstrate the default lazy loading behavior of LINQ to SQL.I want to get the Supplier's contact name and the number of the products he/she supplies us.The second thing I want returned, lives in the related table of Products.

In the Page_Load event handling routine of the Default.aspx type

      NorthwindDataContext ctx = new NorthwindDataContext();

        

            var mysuppliers = from suppl in ctx.Suppliers
                             select suppl;

          

          foreach (var supplier in mysuppliers)
            {
                Response.Write(supplier.ContactName);
                Response.Write("--");
                Response.Write(supplier.Products.Count().ToString());
                Response.Write("<br/>");
            }
 

 5) Before you run your application, in order to understand what hits your database and how often it does it, launch Profiler and start a new trace. Make sure you have included the events RPC:Starting,SQL:BatchStarting,SQL:BatchCompleted.

 6) Run your application and see the results appearing in your screen but at the same time notice the number of sql queries hitting your database. In my case it looks something like this

• exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
  FROM [dbo].[Products] AS [t0]
  WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=1 

then I have a huge number of almost identical dynamic sql queries hitting my database.

• exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
  FROM [dbo].[Products] AS [t0]
  WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=2

• exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
  FROM [dbo].[Products] AS [t0]
  WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=3
  

• exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
  FROM [dbo].[Products] AS [t0]
  WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=4

and the story goes on and on.They are all identical just passing in a new SupplierID value. This is not good for my application.

7) We can change this so we do not hit our database so many times.In the Page_Load event handling routine type (immediately after the NorthwindDataContext ctx = new NorthwindDataContext();) those two lines below:

            DataLoadOptions dlo = new DataLoadOptions();

            dlo.LoadWith<Supplier>(s => s.Products);

             ctx.LoadOptions = dlo;

 Do not forget to include the using System.Data.Linq;

We are basically telling LINQ engine that whenever it does fetch data from our database regarding Supplier entity/table, to fetch the Products related data as well.

8) Run your application again. Before you do that make sure you start a new trace with the events RPC:Starting,SQL:BatchStarting,SQL:BatchCompleted included.

You will see the results in your screen but have a look at the Trace output as well. We do not have so many sql statements anymore hitting our database.The one that appears in my case is this one

SELECT [t0].[SupplierID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax], [t0].[HomePage], [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID] AS [SupplierID2], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], (
    SELECT COUNT(*)
    FROM [dbo].[Products] AS [t2]
    WHERE [t2].[SupplierID] = [t0].[SupplierID]
    ) AS [value]
FROM [dbo].[Suppliers] AS [t0]
LEFT OUTER JOIN [dbo].[Products] AS [t1] ON [t1].[SupplierID] = [t0].[SupplierID]
ORDER BY [t0].[SupplierID], [t1].[ProductID]

9) So now we have instead of lazy loading, eager loading.Hope it all makes sense now. The main point is to know how everything works ( in this case LINQ to SQL ) and how this affects the performance of our application.

Email me if you need the source code.

Hope it helps.