Blog

LINQ to SQL Read-Only Queries

Excerpt by Ken Getz and Robert Green

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.

TopicLINQtoSQLimg1

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

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



Ken Getz

Ken Getz is a featured instructor for several of our Visual Studio courses. He is a Visual Basic and Visual C# expert and has been recognized multiple times as a Microsoft MVP. Ken is a seasoned instructor, successful consultant, and the author or co-author of several best-selling books. He is a frequent speaker at technical conferences like Tech-Ed, VSLive, and DevConnections and he has written for several of the industry's most-respected publications including Visual Studio Magazine, CoDe Magazine, and MSDN Magazine.

Robert Green

Robert Green is a Visual Studio expert and a featured instructor for several of our Visual Basic and Visual C# courses. He is currently a Technical Evangelist in the Developer Platform and Evangelism (DPE) group at Microsoft. He has also worked for Microsoft on the Developer Tools marketing team and as Community Lead on the Visual Basic team. Robert has several years of consulting experience focused on developer training and is a frequent speaker at technology conferences including TechEd, VSLive, VSConnections, and Advisor Live.


This course excerpt was originally posted October 01, 2013 from the online courseware LINQ Using Visual C# 2010 by Ken Getz and Robert Green

C#SQL ServerLINQ