Deferred Query vs Immediate Query Execution in LINQ

To provide an overview, deferred query execution means execution of the SQL query only when it is needed, rather then executing it prior to any operation that may require any change (as you will learn later in this article).

On the other hand, Immediate query execution means execution of the LINQ query immediately when it is executed.

To demonstrate this, I will 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 against the database until the object created is iterated by the user. For example, in the following code, I have created a SampleClass with Id and SentenceValues attributes. I will populate the list and iterate it to display the results. See the results below:



The results here are displayed as expected. But this does not prove that the query was executed when the foreach loop was executed or the query execution was deferred until 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 will 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 will see that the query was prepared before the new items were added to 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 let's move on to the concept of Immediate execution.

Immediate Query Execution

For immediate query execution, the idea is the use of extension methods like .Count() or .ToList() etc, when the query is being created.

Continuing with the code above, let's modify the filterData query that 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 the case of an 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 it in the SQL Profiler. In the case of a deferred query, we could have omitted the foreach loop and removed the .ToList() extension method.

In the 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.

Happy Coding...!!!