When we think in terms of database performance, there is an important characteristic to emphasize and that is: What are the different behaviors of query execution? Particularly, in the .NET framework component, LINQ, there are two different behaviors of query execution developers should be aware of: deferred and immediate.
In this programming tutorial, we will discuss how the deferred query execution and immediate query execution work in LINQ and .NET. In addition, we will discuss the differences between these two types of query executions and examine some .NET sample code for each.
Read: Best Online Courses to Learn .NET
What is Deferred Query Execution in LINQ?
Deferred execution means a query is executed whenever iteration is applied to the query variable. Note, however, that it will not execute when we create the query variable.
To better understand this behavior, consider the following code example:
class Product{ public int id { get; set;} public string Name { get; set;} public double Price { get; set;} } static void Main(String args[]){ var prdList = new List( new Product[] { new Product { Id=1, Name=”Dart”, Price=4.5}, new Product { Id=2, Name=”Mouse Pad”, Price=2.3}, new Product { Id=3, Name=”Toothpaste”, Price=1.2}, new Product { Id=4, Name=”Shampoo”, Price=3.5}, )}; var list = from p in prdList where p.Price > 3.0 select new { p.Name } ; foreach(var l in list) { Console.writeline(l.Name); Console.ReadLine(); } }
Running this code in your integrated development environment (IDE) or code editor would result in the following output:
Dart Shampoo
If you look at the code example above, you might be guessing the query is executed at the point where we assigned the query result to the variable. However, this is not true.
Instead, the query is executed when we iterate the query variable using a foreach loop. This is known as deferred execution.
To better grasp this, let’s examine another code example, which shows a deferred execution query in LINQ. To start, we will create another Product instance, just after the creation of the query variable:
var list = from p in prdList where p.Price > 3.0 select new { p.Name } ; prdList.Add(new Product { Id = 5, Name=”Perfume”, Price=4.0 }); 🡨 Deferred Execution foreach(var l in list) { Console.writeline(l.Name); Console.ReadLine(); }
In the above code example, we created a new Product instance just after the query variable is created. Had the query been executed when we created our query variable, the results would be the same as the one we got in our first code example, which means only two products would meet the criteria of Price > $3.0.
However, when you run the program above, you get a different result, as the query executes after the loop instead:
Dart Shampoo Perfume
Here, the execution of the query was deferred until the query variable was iterated over using a foreach loop.
Deferred execution gives flexibility to C# developers by allowing them to construct queries in several steps and separates both the query building and query execution. One of its important use cases is to fetch the latest information from a database that is being updated at frequent intervals.
Read: Best C# Tools for Code Quality
Immediate Query Execution in LINQ
Another method for querying in LINQ is immediate. In this method, developers force a query to execute immediately. It is useful in cases where programmers need to cache the queries.
Let us understand this concept with our previous example. Let’s add a scenario where we need to display the total number of products that match the criteria:
class Product{ public int id { get; set;} public string Name { get; set;} public double Price { get; set;} } static void Main(String args[]){ var prdList = new List( new Product[] { new Product { Id=1, Name=”Dart”, Price=4.5}, new Product { Id=2, Name=”Mouse Pad”, Price=2.3}, new Product { Id=3, Name=”Toothpaste”, Price=1.2}, new Product { Id=4, Name=”Shampoo”, Price=3.5}, )}; var counter = (from p in prdList where p.Price > 3.0 select p).Count(); 🡨 Immediate Execution prdList.Add(new Product { Id = 5, Name=”Perfume”, Price=4.0 }); Console.writeline(“Number of Products whose price is greater than $3.0 is {0}: , counter); Console.ReadLine(); }
In the above code, to count the number of products that match the condition, the query must be executed. And, in this case, the query gets executed automatically when the Count( ) method is invoked.
As we are adding a new product instance after the query variable is declared, this would have no effect here, as the query is already executed. So, the output would be 2 in this case.
Final Thoughts on LINQ Query Executions
In this .NET programming tutorial, we learned about both query execution methods offered by LINQ. The basic difference between the two is that the deferred execution of queries produces a sequence of values, whereas immediate queries get executed immediately and return a single value.</p.
Hopefully, this article has helped novice developers, as well as veterans, understand the fundamentals of query execution methods in LINQ, the basic differences between the two, and their use cases.
Read more .NET programming tutorials and software development guides.