LearnNowOnline

Blog

LINQ to SQL Read-Only Queries

Excerpt by Ken Getz and Robert Green | October 01, 2013

If you are looking for maximum performance, you can execute your queries in read-only mode. To do that, you can set the ObjectTrackingEnabled property of the DataContext to false. This turns off change tracking, which has some overhead. Because LINQ to SQL does not track changes in this mode, performance improves. When you select Read Only Queries in the chapter's sample application, you will execute two queries. The first retrieves a list of customers in the United States.

nwindDataContext.ObjectTrackingEnabled = false; var usaCustomers = from customer in nwindDataContext.Customers where customer.Country == "USA" select new { customer.CompanyName, customer.City, customer.Region };

The second query illustrates an important consequence of setting ObjectTrackingEnabled to false: It sets DeferredLoadingEnabled to false and therefore turns off deferred loading.

nwindDataContext.ObjectTrackingEnabled = false; var customer = nwindDataContext.Customers.Single( c => c.CustomerID == "ALFKI"); DisplayHeader("Information for ALFKI"); Console.WriteLine("Company name: {0}", customer.CompanyName); Console.WriteLine("City: {0}", customer.City); Console.WriteLine("Region: {0}", customer.Region); Console.WriteLine(); Console.WriteLine("Orders placed: {0}", customer.Orders.Count()); var orderSummaries = from order in customer.Orders select new { Order = order.OrderID, Value = order.Order_Details.Sum( d => d.Quantity * d.UnitPrice) }; Console.WriteLine(); Console.WriteLine("Orders summary"); Console.WriteLine(new String('=', 14)); foreach (var order in orderSummaries) { Console.WriteLine(" Order {0} for {1:C}", order.Order, order.Value); }

Figure below shows the result of running this code.

Figure above Deferred loading is disabled, so the number of orders does not display.

The code first executes a query to retrieve information for a customer. It then displays the customer's name, city, and region. Next, the code displays the number of orders for this customer. In the previous example, LINQ to SQL then generated a SQL statement to retrieve the customer's orders. However, when you turn off change tracking you turn off deferred loading, so in this case, LINQ to SQL does not send the SQL statement to retrieve the customer's orders. The code next defines the following query to retrieve the ID and value for each order. However, customer.Orders is empty, so LINQ to SQL does not generate the SQL statement to retrieve the data.

var orderSummaries = from order in customer.Orders select new { Order = order.OrderID, Value = order.Order_Details.Sum( d => d.Quantity * d.UnitPrice) };

TIP: Use read-only queries with caution. The absence of deferred loading means you will not retrieve all of the data you would otherwise. If your code relies on the missing data, you could receive runtime errors

.This post is an excerpt from the online courseware for our Microsoft LINQ Using Visual C# 2010 course written by expert Ken Getz.