Use Of Deferred And Immediate Query Execution In LINQ

Introduction

Here, you will learn about query execution in linq and what deffered query execution means, also learning immediate query execution and it's differences, we will see with example for better understanding.

 

Prerequisite 

• Visual Studio
• Basics of linq and c#  

Article Flow  

• Use of Deferred query execution
• Implement deferred query execution in custom extension method
• Use of Immediate query execution  
• Difference between Deferred and Immediate Query execution

Use of Deferred query execution 

Deferred query execution represents "It delays the excution until it's realized data is actually required", so that we can avoid un-necessary execution and improve the performace like lazy loading, okay let's see the example to get clear picture about this, for that i created below and class and created list.

public class Employee {
    public int ID {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public string Designation {
        get;
        set;
    }
    public double Salary {
        get;
        set;
    }
}
List < Employee > employeeDetails = new List < Employee > () {
    new Employee() {
            ID = 101, Name = "Gnanavel Sekar", Designation = "Software Engineer", Salary = 150000
        },
        new Employee() {
            ID = 102, Name = "Subash S", Designation = "Software Engineer", Salary = 100000
        },
        new Employee() {
            ID = 103, Name = "Robert S", Designation = "Software Engineer", Salary = 70000
        },
        new Employee() {
            ID = 103, Name = "Ammaiyappan K", Designation = "Software Devloper", Salary = 80000
        }
};

Now let's see the deferred quey unique functionality, for that now am going to filter the employee who getting more than 90k salary,

var moreThan90kEmployee = from result in employeeDetails
where result.Salary > 90000
select result;

In above code i have written the query to get the employee who getting more than 90k salary, okay now see what's the result int "moreThan90kEmployee " variable

See the above screen still doesn't executed, okay when it will be executed?

foreach (var item in moreThan90kEmployee)
{
   Console.WriteLine("ID :" + item.ID + ", Name :" + item.Name + ", Designation :" + item.Designation + ", Salary :" + item.Salary);
}

Now run your application

 

In the above example, you can see the query is materialized and executed when you iterate using the foreach loop. Here we got the employee list for whoever makes more than a 90k salary, this is called deferred execution. Linq process the employeeDetails collections when we actually access each object from the collection and do something using that respctive collection object, here we seen the deferred query doesn't return the result until the object are accessed or something doing with it.

 

Shall we try by modifing the collection, and check whether it's provide the old collection or latest updated collection data ? for that now am going to add one employee into that respective collection.

employeeDetails.Add(new Employee {
    ID = 104, Name = "Ramar A", Designation = "Tech Lead", Salary = 200000
});

And now load the deffered query whatever we already created and assigned in "moreThan90kEmployee "

foreach(var item in moreThan90kEmployee)
{
    Console.WriteLine("ID :" + item.ID + ", Name :" + item.Name + ", Designation :" + item.Designation + ", Salary :" + item.Salary);
}

Now run your application.

 

 

Here we got the result with latest data and we didn't write the query again to get the employees whoever more than 90k. Now we got the clear picture about deferred query execution, we can reuse the query by calling the same object and it will not return the result until we access the each object or something doing with it.

 

Overview

 

 

Implement deferred query execution in custom extension method

 

You may ask why we want to create deffered query execution in the custom extension method? Because, extension method represents reusable and the deferred query also reusable query so that we will write it in extension method.

 

if you want to know custom extnsion method, refer here.

 

Okay now we move to the implementation, we can implement the deferred execution for our custom extension methods for IEnumerable using the yield keyword from C#. For example we will create a custom extension method "EmployeeMoreThan90k" for IEnumerable that returns a list of all employee who are more than 90k salary getting.

public static class ExtensionMethods {
    public static IEnumerable < Employee > EmployeeMoreThan90k(this IEnumerable < Employee > employee) {
        foreach(Employee emp in employee) {
            Console.WriteLine("Accessing Employee Name {0}", emp.Name);
            if (emp.Salary > 90000) yield
            return emp;
        }
    }
}

See above custom extension method "EmployeeMoreThan90k", the employee names are printed whenever the "EmployeeMoreThan90k"  custom method called, okay now we will see with example.

List < Employee > employeeDetails = new List < Employee > () {
    new Employee() {
            ID = 101, Name = "Gnanavel Sekar", Designation = "Software Engineer", Salary = 150000
        },
        new Employee() {
            ID = 102, Name = "Subash S", Designation = "Software Engineer", Salary = 100000
        },
        new Employee() {
            ID = 103, Name = "Robert S", Designation = "Software Engineer", Salary = 70000
        },
        new Employee() {
            ID = 103, Name = "Ammaiyappan K", Designation = "Software Devloper", Salary = 80000
        }
};
var employeeMoreThan90ks = from employee in employeeDetails.EmployeeMoreThan90k()
select employee;
foreach(Employee empDetail in employeeMoreThan90ks) {
    Console.WriteLine("Name: {0}, Salary: {1}", empDetail.Name, empDetail.Salary);
}

Result

 

 

In above result we can realize that as the extension method method executed for each time when the respective(employeeMoreThan90ks) deferred query is executed, EmployeeMoreThan90k() is getting called when you iterate employeedetails using the foreach loop.This way we can create custom methods using the yield keyword to get the advantage of deferred execution.Deferred execution is important as it gives you the flexibility of constructing the query in several steps by separating query construction from query execution.

 

Overall Code for deferred execution,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace QueryExecutionInLINQ {
    class Program {
        static void Main(string[] args) {
            List < Employee > employeeDetails = new List < Employee > () {
                new Employee() {
                        ID = 101, Name = "Gnanavel Sekar", Designation = "Software Engineer", Salary = 150000
                    },
                    new Employee() {
                        ID = 102, Name = "Subash S", Designation = "Software Engineer", Salary = 100000
                    },
                    new Employee() {
                        ID = 103, Name = "Robert S", Designation = "Software Engineer", Salary = 70000
                    },
                    new Employee() {
                        ID = 103, Name = "Ammaiyappan K", Designation = "Software Devloper", Salary = 80000
                    }
            };
            var moreThan90kEmployee = from result in employeeDetails
            where result.Salary > 90000
            select result;
            foreach(var item in moreThan90kEmployee) {
                Console.WriteLine("ID :" + item.ID + ", Name :" + item.Name + ", Designation :" + item.Designation + ", Salary :" + item.Salary);
            }
            employeeDetails.Add(new Employee {
                ID = 104, Name = "Ramar A", Designation = "Tech Lead", Salary = 200000
            });
            foreach(var item in moreThan90kEmployee) {
                Console.WriteLine("ID :" + item.ID + ", Name :" + item.Name + ", Designation :" + item.Designation + ", Salary :" + item.Salary);
            }
            var employeeMoreThan90k = from employee in employeeDetails.EmployeeMoreThan90k()
            select employee;
            foreach(Employee empDetail in employeeMoreThan90k) {
                Console.WriteLine("Name: {0}, Salary: {1}", empDetail.Name, empDetail.Salary);
            }
        }
    }
    public class Employee {
        public int ID {
            get;
            set;
        }
        public string Name {
            get;
            set;
        }
        public string Designation {
            get;
            set;
        }
        public double Salary {
            get;
            set;
        }
    }
    public static class ExtensionMethods {
        public static IEnumerable < Employee > EmployeeMoreThan90k(this IEnumerable < Employee > employee) {
            foreach(Employee emp in employee) {
                Console.WriteLine("Accessing Employee {0}", emp.Name);
                if (emp.Salary > 90000) yield
                return emp;
            }
        }
    }
}

Use of Immediate query execution

 

Immediate query execution reverse of deferred query execution because it's forcing the linq to provide the immediate result and it always deals with the predefined extension method. We will see the example to get clear picture about this.

List < Employee > employeeDetails = new List < Employee > () {
    new Employee() {
            ID = 101, Name = "Gnanavel Sekar", Designation = "Software Engineer", Salary = 150000
        },
        new Employee() {
            ID = 102, Name = "Subash S", Designation = "Software Engineer", Salary = 100000
        },
        new Employee() {
            ID = 103, Name = "Robert S", Designation = "Software Engineer", Salary = 70000
        },
        new Employee() {
            ID = 103, Name = "Ammaiyappan K", Designation = "Software Devloper", Salary = 80000
        }
};
var moreThan90kEmployees = (from result in employeeDetails where result.Salary > 90000 select result).ToList();

Same query I am writing here to get the employees who are more than 90k salary, here we can use the conversion operator(ToList(),ToArray(),etc) to get the immediate result, not only with conversion operator we can get the immediate result by using all aggregate function also. 

 

 

 

For clear understanding see one more example,

var moreThan90kEmployeeCount= (from result in employeeDetails;
where result.Salary > 90000;
select result).Count();

Result

 

 

In above example we got the immediate result of count who are more than 90k salary

 

Now we got the clear picture about immediate query execution, it's used to return the result immediately by using conversion operator, aggregate function, and  more predefined functions.

 

Overall code for immediate query execution,

List < Employee > employeeDetails = new List < Employee > () {
    new Employee() {
            ID = 101, Name = "Gnanavel Sekar", Designation = "Software Engineer", Salary = 150000
        },
        new Employee() {
            ID = 102, Name = "Subash S", Designation = "Software Engineer", Salary = 100000
        },
        new Employee() {
            ID = 103, Name = "Robert S", Designation = "Software Engineer", Salary = 70000
        },
        new Employee() {
            ID = 103, Name = "Ammaiyappan K", Designation = "Software Devloper", Salary = 80000
        }
};
var moreThan90kEmployees = (from result in employeeDetails where result.Salary > 90000 select result).ToList();
var moreThan90kEmployeeCount = (from result in employeeDetails where result.Salary > 90000 select result).Count();

Difference between Deferred and Immediate Query execution

 

Moreover we seen both uses so we can easily list out.

 

Deferred Query Execution	Immediate Query Execution
A query is not executed at the point of its declaration. It is executed when the Query variable is iterated by using loop like as for, foreach.	A query is executed at the point of its declaration. The query which returns a singleton value (a single value or a set of values) like Average, Sum, Count, List,etc
It provides the facility of query reusability, since it always fetches the updated data from the data source which exists at the time of each execution.	It doesn't provide the facility of query re-usability since it always contains the same data which is fetched at the time of query declaration
We can force a query to execute immediately by using the foreach or for.	We can force a query to execute immediately of by calling ToList, ToArray methods(conversion operators,aggregate operators)

 

Summary

 

In this aricle we learned the use of deferred query execution, how to use the deferred query in custom extension methods and the use of immediate query execution and their differences.

 

I hope it was helpful, your comments and feeback are always welcome.