Recently i added an article related to lady/eager loading in LINQ. This concept is quite important in terms of the performance of the database. During this, i also got a chance to explore the concept of the Deferred and Immediate query execution.
To give an overview, deferred query execution means execution of SQL query only at the time when it is required to be, rather then executing it prior to any operation which may require any change (will you will come to know later in this article).
On the other hand, Immediate query execution means execution of the LINQ query immediately when it is being written.
To demonstrate this, I will again create a sample console application. Then check out the results. So let’s start by creating a sample console application.
Deferred Query Execution :
In deferred query execution, the linq query is not sent or executed into database until the object created is iterated by the user. For ex : In my following code, I have created a SampleClass with Id and SentenceValues attributes. I will populate the list and iterate the same to display the results. See the results below :
The results here are displayed as expected. But this does not proves anything that the query was executed at the time when the foreach loop was executed or the query execution was deferred till that time.
Now to test this, we will add 2 more items in the list after the filterData is created but just before the foreach loop is being executed. Then run the application and you can see that the new item is also included in the list. So this will prove that the query was sent to the database when the loop was executed. See the results below :
So here you can see that the query was prepared before the new items were added in the list but it was executed when the loop was iterated. As a result new items were included in the results. This is the concept of the Deferred execution.
Now lets move on to the concept of Immediate execution.
Immediate Query Execution :
For immediate query execution, the basic behind this is the use of extension methods like .Count() or .ToList() etc, when the query is being created.
Continuing with the above code, lets modify the filterData query which is being generated. Let’s add the .ToList() extension method and see the results.
So as you can see, we have added the .ToList() extension method to the query being prepared. This causes the query to be prepared and executed at the same time. So even if we add the new items to the list and try to iterate them, they are not included in the result list. So this is the concept of the Immediate Query Execution.
You could have tested the same concept of the query execution by using any .edmx model and testing it against the SQL profiler. In case of immediate query, we would be required to write the code up-to the the var filterData line using the .ToList() extension method and then check the same in the SQL Profiler. In case of deferred query, we could have omitted the foreach loop and removed the .ToList() extension method.
In first case, you would see that the SQL query is being generated in the profiler but not in the second case. Let me tell you, I have not tried this but thus should work this way only, as per these concepts.