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.
We have created three entities as per the table design i.e. ‘ClsCustomerWithAddresses’,’ClsAddresses’ and ‘ClsPhone’. We have defined the relationships between them using ‘EntitySet’ and ‘EntityRef’.
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.
Analyzing the LINQ SQL round trips
Ok, now that we have analyzed that it takes 5 steps to execute a LINQ query. So let’s try to figure out on which step does the LINQ query actually fire SQL to the database. So what we will do is we will run the above LINQ code and analyze the same using SQL profiler.
Just so that we do not catch with lot of SQL Server noise we have only enabled RPC and SQL batch events.
Now when you run the query you will find the below things:-
• The execution of actual SQL takes place when the for each statement is iterated on the LINQ objects.
• The second very stunning thing you will notice is that for every entity 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 lot of round trips.
Asked In: Many Interviews |