Click here to Skip to main content
15,867,568 members
Articles / Web Development / ASP.NET

Optimizing LINQ Queries using DataLoadOptions

Rate me:
Please Sign up or sign in to vote.
4.86/5 (25 votes)
5 Jul 2009CPOL4 min read 89.9K   950   65   12
Optimizing LINQ Queries using DataLoadOptions

Table of Contents

Introduction

Thanks to everyone who has knowingly / unknowingly helped me to become a Microsoft MVP, hope I can live up to it.

In this section, we will understand the round trip issues of LINQ and how we can overcome the same using ‘DataLoadOptions’. One of the biggest issues with LINQ to SQL is that it fires SQL query for every object which has a huge impact on performance. In this article, we will see how we can get all data in one SQL query.

LINQ Basics

This article assumes that you have basic knowledge of how entity objects can be flourished using LINQ. In case you are not aware of basics of LINQ to SQL mapping, you can read my article to understand the basic LINQ concepts.

Customer, Addresses and Phones LINQ Entities

First let’s try to understand how LINQ queries actually work and then we will see how round trips happen. Let’s consider the below database design where we have 3 tables -- customer, addresses and phone. There is one-many relationship between customer and addresses, while there is one-one relationship between address table and phones.

Image 1

We have created three entities as per the table design:

  • ClsCustomerWithAddresses
  • ClsAddresses
  • ClsPhone

We have defined the relationships between them using ‘EntitySet’ and ‘EntityRef’.

Click to enlarge image

To fill the entity objects with data from table is a 5 step process. As a first step, the datacontext connection is created using the connection string, LINQ query is created and then we start browsing through customer, address and phones.

Click to enlarge image

Analyzing the LINQ SQL Round Trips

Ok, now that we have analyzed that it takes 5 steps to execute a LINQ query, let’s try to figure out on which step the LINQ query actually fires SQL to the database. So what we will do is run the above LINQ code and analyze the same using SQL profiler.

Just so that we do not catch a lot of SQL Server noise, we have only enabled RPC and SQL batch events.

Image 4

Now when you run the query, you will find the below things:

  • The execution of actual SQL takes place when the foreach statement is iterated on the LINQ objects.
  • The second very stunning thing you will notice is that for every entity, a separate query is fired to SQL Server. For instance, for customer one query is fired and then separate queries for address and phones are fired to flourish the entity object. In other words, there are a lot of round trips.

Click to enlarge image

Avoiding Round Trips using DataLoadOptions

We can instruct LINQ engine to load all the objects using ‘DataLoadOptions’. Below are the steps involved to enable ‘DataLoadOptions’.

The first step is to create the data context class:

C#
DataContext objContext = new DataContext(strConnectionString);

The second step is to create the ‘DataLoadOption’ object:

C#
DataLoadOptions objDataLoadOption = new DataLoadOptions();

Using the LoadWith method, we need to define that we want to load customer with address in one SQL.

C#
objDataLoadOption.LoadWith<clsCustomerWithAddresses>
	(clsCustomerWithAddresses => clsCustomerWithAddresses.Addresses);

Every address object has phone object, so we have also defined saying that the phone objects should be loaded for every address object in one SQL.

C#
objDataLoadOption.LoadWith<clsAddresses>(clsAddresses => clsAddresses.Phone);

Whatever load option you have defined, you need to set the same to the data context object using ‘LoadOptions’ property.

C#
objContext.LoadOptions = objDataLoadOption;

Finally prepare your query:

C#
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerWithAddresses>()
select objCustomer;

Start looping through the objects:

C#
foreach (clsCustomerWithAddresses objCustomer in MyQuery)
{
    Response.Write(objCustomer.CustomerName + "<br>");

    foreach (clsAddresses objAddress in objCustomer.Addresses)
    {
        Response.Write("===Address:- " + objAddress.Address1 + "<br>");
        Response.Write("========Mobile:- " + objAddress.Phone.MobilePhone + "<br>");
        Response.Write("========LandLine:- " + objAddress.Phone.LandLine + "<br>");
    }
}

Below is the complete source code for the same:

C#
DataContext objContext = new DataContext(strConnectionString);
DataLoadOptions objDataLoadOption = new DataLoadOptions();
objDataLoadOption.LoadWith<clsCustomerWithAddresses>
	(clsCustomerWithAddresses => clsCustomerWithAddresses.Addresses);
objDataLoadOption.LoadWith<clsAddresses>(clsAddresses => clsAddresses.Phone);
objContext.LoadOptions = objDataLoadOption;
var MyQuery = from objCustomer in objContext.GetTable<clsCustomerWithAddresses>()
select objCustomer;

foreach (clsCustomerWithAddresses objCustomer in MyQuery)
{
    Response.Write(objCustomer.CustomerName + "<br>");

    foreach (clsAddresses objAddress in objCustomer.Addresses)
    {
        Response.Write("===Address:- " + objAddress.Address1 + "<br>");
        Response.Write("========Mobile:- " + objAddress.Phone.MobilePhone + "<br>");
        Response.Write("========LandLine:- " + objAddress.Phone.LandLine + "<br>");
    }
}

Abracadabra…. Now if you run the code LINQ has executed only one SQL with proper joins as compared to 3 SQL for every object shown previously.

Click to enlarge image

Source Code

We have also attached a source code. Run the project and see how the profiler shows different SQL execution. You can first run the ‘EntitySet’ example and see how SQL profiler reacts for the same and then run the example with ‘DataLoadOptions’. The SQL script is attached in a different file.

))==:- You can the download the source code from here.

Image 7

For further reading do watch the below interview preparation videos and step by step video series.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect https://www.questpond.com
India India

Comments and Discussions

 
Questionstill not entirely working Pin
jasie289-Jun-15 5:03
jasie289-Jun-15 5:03 
QuestionUpdate Pin
Member 1168549112-May-15 10:06
Member 1168549112-May-15 10:06 
GeneralAmazing Article Pin
arvind mepani9-Jul-14 19:03
arvind mepani9-Jul-14 19:03 
QuestionPrecise and clear Pin
kazzak18-Jun-14 13:19
kazzak18-Jun-14 13:19 
QuestionDataLoadOptions is generationg complex query Pin
laskh29-Sep-12 1:45
laskh29-Sep-12 1:45 
GeneralMy vote of 5 Pin
samanthikavenkatesh5-Sep-12 18:54
samanthikavenkatesh5-Sep-12 18:54 
QuestionHow to use it... Pin
spins312-Sep-11 1:16
spins312-Sep-11 1:16 
Generalsimple challenge Pin
NameFlorin1-Sep-10 23:41
NameFlorin1-Sep-10 23:41 
GeneralCongratulation Pin
Abhijit Jana7-Jul-09 11:02
professionalAbhijit Jana7-Jul-09 11:02 
GeneralRe: Congratulation Pin
Shivprasad koirala7-Jul-09 16:02
Shivprasad koirala7-Jul-09 16:02 
GeneralFormatting problems Pin
Henry Minute5-Jul-09 5:35
Henry Minute5-Jul-09 5:35 
GeneralRe: Formatting problems Pin
Shivprasad koirala5-Jul-09 7:17
Shivprasad koirala5-Jul-09 7:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.