You might be making a lot of unneccessary trips to your DB with Linq-to-SQL

Linq-to-SQL has great use of relations, but lazy loading can cause a huge amount of trips to your DB - implementing eager loading is one answer

After my rant on L2E, Dan from Microsoft got back to me in the comments and pointed out a lot of what I wanted to know. One thing that he brought up was the use of eager loading which I hadn't looked in to; turns out it can be VERY important!

Let's say you are using a GridView and for each row, you are using a relation to the 'states' table to pull the name of a 'state' object:
<asp:BoundField DataField="item.state.state_name" HeaderText="State Name" SortExpression="item.state.state_name" />

Now this will work just fine, but each time you add a row, there is going to be another call to the DB to get the state name. Now if you only have a few rows, no big deal, but what if you are calling 100 records each with a a call to the state name and a country name? It is no longer one trip to get the items, it grows huge! Here is some ugly psuedo-code for understanding:
ONE_SQL_QUERY -> 100_ROWS_RETURNED (1 trip)
FOREACH(ROW row in 100_ROWS_RETURNED)
{
  QUERY_FOR_STATE_NAME (1 trip)
  QUERY_FOR_COUNTRY_NAME (1 trip)
}

That one query has now jumped to 201 database calls (1+100(1+1)) ... that is no small difference! Now, that first query, no matter how big and ugly it is is dwarfed by these petty little calls for one small string. Just imagine what happens if you are calling multiple and nested relations, that one call can easily jump to well over 1000 in no time.

So what to do?

While searching on Linq-to-Entities (yes, I still want to make the switch some time) I came across this: http://blogs.msdn.com/adonet/archive/2008/10/07/migrating-from-linq-to-sql-to-entity-framework-eager-loading.aspx which shows how you deal with this in both L2S and L2E (also answered my last question on my rant against L2E. This is actually a situation where (I think) L2E shines over L2S, but that's a whole other debate. Here is what you would need to do.
dbDataContext db = new dbDataContext();
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<item>(i => i.state);
dlo.LoadWith<item>(i => i.country);
db.LoadOptions = dlo;

What this is doing is telling your DataContext to load those other elements with your every 'item' you load (don't forget to include using System.Data.Linq;). This will obviously make for a larger query to begin with, but it will cut out those multiple return trips that are the real problem!

On an somewhat related note... I am going to force myself to use L2E on my next project.
Comments are closed