LINQ to SQL : Understanding Query Execution

Query Execution

Whether you write a query as a high-level query expression or build one out of the individual operators, the query that you write is not an imperative statement executed immediately. It is a description. For example, in the declaration below the local variable q refers to the description of the query not the result of executing it.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

The actual type of q in this instance is IQueryable<Customer>. It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur.

An IQueryable object is similar to an ADO.NET command object. Having one in hand does not imply that a query was executed. A command object holds onto a string that describes a query. Likewise, an IQueryable object holds onto a description of a query encoded as a data structure known as an Expression. A command object has an ExecuteReader() method that causes execution, returning results as a DataReader. An IQueryable object has a GetEnumerator() method that causes the execution, returning results as an IEnumerator<Customer>.

Therefore, it follows that if a query is enumerated twice it will be executed twice.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute first time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);
// Execute second time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

This behavior is known as deferred execution. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it.

Of course, application writers often need to be very explicit about where and when a query is executed. It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.

To avoid executing multiple times convert the results into any number of standard collection classes. It is easy to convert the results into a list or array using the standard query operators ToList() or ToArray().

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute once using ToList() or ToArray()
var list = q.ToList();
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);

This post is modified version of article "Introduction to LINQ" given at MSDN

Namaste !

Anugrah Atreya