Differed Execution (vs) Immediate execution in LINQ

Recently while understanding LINQ, many things unfurled from MSDN. The main theme of .NET is lazy binding (or) lazy loading. And it is evolving in all the ways within the .net Framework. I recollect in one of my previous posts about the mainly concentrated theme for every version that is released. I could be wrong from the insider perspective, but what I’ve observed is what I’ve written.

Anyhow, coming back to the main purpose of this post, which is all about the lazy loading and the point behind various execution mechanisms that are available within LINQ. Before I proceed further, one point is very certain that, choosing LINQ is costlier from the point of “Response Time” from DB to any connected system. This statement is true when compared the LINQ with any traditional mechanism of database connectivity.

Traditionally, every developer writes code with Connection object for connecting to the Data Store and then disposes the connection. This method can be by various objects that are provided within .net framework. One of the mechanism is that

• Use a connection object and DataAdapter
• Use a connection object along with Command and use either DataReader or DataSet

These mechanisms are pretty neat and straight, while connecting to the datastore. Where as they don’t provide the query mechanism to the developer against the returned results from the datastore. Ofcourse, you still could do the “Filter” method for the dataset or datatable object. This again doesn’t give the generic way to query from the data in the dataset / datatable.

LINQ, in this context provides a great flexibility for the developer to query against the data schema and provides a decent means of all kinds of DataBase querying techniques. In other words, I could say that LINQ gives the power of Database Querying to the C# developer. If your applications are developed using LINQ, you don’t need to have a DB developer. But you need the DB administrator to understand the health of the database and fine tune the queries used by LINQ. Hence, by choosing LINQ for your projects you are saving cost to the project owner. So, To LINQ or NOT to is upto you to decide. Let me not write more here about LINQ and jump to the execution mechanisms within LINQ.

As titled in the post, there are 2 mechanisms for the execution of LINQ Results, I say it is about LINQ RESULTS. They are differed execution as well as Immediate execution. The main difference between these two are the lazy loading (or) JustInTime loading at the run time. To make simple this entire story, let me give you some code snippet, so that you would understand. For this am trying to query the Northwind Database.

Requirement:

Display all the employee names along with the product names and total of sold quantity

Solution 1 : In this solution am trying to use the LINQ as an Expression.

var empCounts = from eachEmpData in
(from eachSale in
  (from eachEmp in nwDB.Employees
     join eachOrder in nwDB.Orders on eachEmp.EmployeeID equals eachOrder.EmployeeID
     join eachOrderDetails in nwDB.Order_Details on eachOrder.OrderID equals eachOrderDetails.OrderID
    join eachProduct in nwDB.Products on eachOrderDetails.ProductID equals eachProduct.ProductID
    where eachOrder.OrderDate > dtStart && eachOrder.OrderDate < dtEnd
                         select new
                         {
                             FirstName = eachEmp.FirstName,
                             LastName = eachEmp.LastName,
                             ProductName = eachProduct.ProductName,
                             OrderQuantity = eachOrderDetails.Quantity
                         }
                       )
   group eachSale by new { eachSale.FirstName, eachSale.LastName, eachSale.ProductName } into groupSales
                    select new
                    {
                        groupSales.Key.FirstName,
                        groupSales.Key.LastName,
                        groupSales.Key.ProductName,
                        Sold = groupSales.Sum(qty => qty.OrderQuantity)
                    })
               select eachEmpData;
 
// the below line binds the data from LINQ Expression
bsLINQData.DataSource = empCounts;

In this solution, you could see that I’ve written all the joins similar to that of the Database SQL joins. If you are familiar with all such queries, then it is very easy to write. But this kind of writing is also expensive. The alternative for such queries is the next solution.

Solution 2: In this solution am trying to use the same above LINQ as methods

var empCounts = nwDB.Orders
   .Where(eachOrder => eachOrder.OrderDate > dtStart && eachOrder.OrderDate < dtEnd)
                   .SelectMany(eachOrderDetails => eachOrderDetails.Order_Details)
                   .GroupBy(od => new { od.Product, od.Order.Employee })
                   .Select(od => new
                   {
                       FirstName = od.Key.Employee.FirstName,
                       LastName = od.Key.Employee.LastName,
                       ProductName = od.Key.Product.ProductName,
                       Sold = od.Sum(q => q.Quantity)
                   })
                   .OrderBy(s => s.Sold);

               // the below line binds the data from LINQ Expression
               bsLINQData.DataSource = empCounts;

In this solution, you find less coding and much usage of LINQ as methods. This kind of coding is much better than the previous solution. For the records, the above solution has taken about 3 milliseconds on my laptop where as the current solution took about 1 millisecond to query. See below for the stats and comparison.

Solution 3: The above two solutions are Differed execution, where as the current one is all about Immediate execution.

var empCounts = nwDB.Orders
   .Where(eachOrder => eachOrder.OrderDate > dtStart && eachOrder.OrderDate < dtEnd)
   .SelectMany(eachOrderDetails => eachOrderDetails.Order_Details)
   .GroupBy(od => new { od.Product, od.Order.Employee })
   .Select(od => new
   {
       FirstName = od.Key.Employee.FirstName,
       LastName = od.Key.Employee.LastName,
       ProductName = od.Key.Product.ProductName,
       Sold = od.Sum(q => q.Quantity)
   })
   .OrderBy(s => s.Sold).ToList();

// the below line binds the data from LINQ Expression
bsLINQData.DataSource = empCounts;

Here you have to observe the query ended with .ToList() and that is the trick of this query. When you see the statistics, it is very surprising that the time taken to query is very negligible when compared with binding the query results to the binding source. Which means that it is evident to say, LINQ doesn’t fetch the data when it is queried, but it fetches data when it is actually binded to any source.

Finally, the results when compared the above all the three solutions are as mentioned below

	Query	Bind
Expression	3	60
Methods	1	44
InList	35	9

Conclusion :

Solution 1 and Solution 2 are the examples for Differed Execution, where as the 3rd solution is an example for Immediate execution. Any comments?