There are two ways to check when a query has been executed. The first is:

For example, when the following statement is executed, there is nothing in the Profiler:

IEnumerable<Product> beverages = from p in db.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;

However, when the following statement is being executed, you will see from the profiler that a query has been executed in the database:

Console.WriteLine("There are {0} Beverages", beverages.Count());

The query executed in the database is like this:

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
WHERE [t1].[CategoryName] = @p0
ORDER BY [t0].[ProductName]',N'@p0 nvarchar(9)',@p0=N'Beverages'

The profiler window should look as shown the following image:

From the Profiler, we know that, under the hood, LINQ actually called sp_executesql, and it also used a left outer join to get the categories of products.

Another way to trace the execution time of a LINQ statement is to use the logs. The DataContext class provides a method to log every SQL statement it executes. To see the logs, we can first add the following statement to the beginning of the program, immediately after the Main statement:

db.Log = Console.Out;

Then, we can add the following statement immediately after the variable beverages is defined, but before its count is referenced:

Console.WriteLine("After query syntax is defined, before it is referenced.");

So the first few lines of the program are now:

static void Main(string[] args)
{
// log database query statements to stand out
db.Log = Console.Out;
// CRUD operations on tables
TestTables();
Console.ReadLine();
}
static void TestTables()
deferred execution, LINQ to SQLchecking, SQL logs used{
// retrieve all Beverages
IEnumerable<Product> beverages = from p in db.Products
where p.Category.CategoryName == "Beverages"
orderby p.ProductName
select p;
Console.WriteLine("After query syntax beverages is defined, before it is referenced.");
Console.WriteLine("There are {0} Beverages", beverages.Count());
// rest of the file

Now, if you run the program, the output will look like this:

From the logs, we see that the query is not executed when the query syntax is defined. Instead, it is executed when beverages.Count() is being called.

If the query expression will return a singleton value, the query will be executed as soon as it is defined. For example, we can add this statement to get the average price of all products:

decimal? averagePrice = (from p in db.Products
select p.UnitPrice).Average();
Console.WriteLine("After query syntax averagePrice is defined, before it is referenced.");
Console.WriteLine("The average price is {0}", averagePrice);

The output is like this:

From this output, we know that the query is executed at the same time as the query syntax is defined.

However, just because a query is using one of the singleton methods such as sum, average, or count, this doesn't mean that the query will be executed as soon as it is defined. If the query result is a sequence, the execution will still be deferred. The following is an example of this kind of query:

// deferred execution2
var cheapestProductsByCategory =
from p in db.Products
group p by p.CategoryID into g
select new
{
CategoryID = g.Key,
CheapestProduct =
(from p2 in g
where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
select p2).FirstOrDefault()
};
Console.WriteLine("Cheapest products by category:");
foreach (var p in cheapestProductsByCategory)
{
Console.WriteLine("categery {0}: product name: {1} price: {2}", p.CategoryID, p.CheapestProduct.ProductName, p.CheapestProduct.UnitPrice);
}

If you run the above query, you will see that it is executed when the result is being printed, and not when the query is being defined. An extract of the results looks like this:

From this output, you can see that when the result is being printed, it first goes to the database to get the minimum price for each category. Then, for each category, it goes to the database again to get the first product with that price. In a real application, you probably wouldn't want to write such a complex query in your code. So, you would put it in a stored procedure.