On this subject of O/R Mappers and their ability to be real chatty with your database if you are not careful, I thought I would show some quick examples with regards to LINQ To SQL and iterating against lazy loaded collection properties. For some background on why I am talking about this, see Performance Profiling O/R Mapper Calls to the Database.
LINQ To SQL and Northwind Database
Let's take the good 'ol Northwind Database that still refuses to die in our code examples due to freaks like me. Drop the Orders and Order_Details Tables on the LINQ To SQL Designer Surface and start developing against it in a Console Application in Visual Studio 2008.
Questionable Use of Lazy-Loading
Let's grab the Orders from the Northwind Database as well as a count of the number of Order_Details for each order. The code could look like this:
using (NorthwindDataContext dc = new NorthwindDataContext())
{
dc.Log = Console.Out;
foreach (var order in dc.Orders.ToList())
Console.WriteLine(string.Format("ID: {0}, Total Items:{1}",
order.OrderID, order.Order_Details.Count));
}
The code looks harmless enough, but because by default the Order_Details Child Collection is lazy loaded we have a flourish of queries going back and forth between the application and the database. One query gets all the Orders, but then 1 query gets executed for each order everytime we access the Count Property of Order_Details. This is a mess for such a simple need.
Eager Loading of Order_Details
We can avoid all those extra queries by eager loading the Order_Details Collection using DataLoadOptions and LoadWith
using (NorthwindDataContext dc = new NorthwindDataContext())
{
var lo = new DataLoadOptions();
lo.LoadWith<Order>(o => o.Order_Details);
dc.LoadOptions = lo;
dc.Log = Console.Out;
foreach (var order in dc.Orders.ToList())
Console.WriteLine(string.Format("ID: {0}, Total Items:{1}",
order.OrderID, order.Order_Details.Count));
}
This definitely cuts down on the number of queries as we now only have 1 query total. However, we do have a lot of extra unused data moving between the application and database that we could trim.
Return an Anonymous Type
Let's take the focus off our business entities a moment since it seems we may just be needing this information for reporting needs. Let's return an anonymous type from the query with just the data we need: OrderID and Order_Details.Count.
using (NorthwindDataContext dc = new NorthwindDataContext())
{
dc.Log = Console.Out;
var data = from o in dc.Orders
select new
{
o.OrderID,
o.Order_Details.Count
};
foreach (var item in data.ToList())
Console.WriteLine(string.Format("ID: {0}, Total Items:{1}",
item.OrderID, item.Count));
}
The results again is 1 query, but it returns only the data we need. This is about as clean as it can get.
Conclusion
As you can see one needs to really look ( profile ) the communications between the O/R Mapper and the database to see what is happening behind the scenes. Again, don't go all nutty on me and create a maintenance nightmare trying to optimize every ounce of the O/R Mapper's calls, but do understand what roundtrips are occuring and data is being retrieved to verify you aren't creating a performance nightmare that can be alleviated due to a few simple adjustments as mentioned above. In this case I am just using the Logging Ability of the DataContext Class to show me the resulting queries, but you can also use SQL Server Profiler, ANTS Profiler, dotTrace, NHibernate Profiler, or whatever other tool you have at your disposal that makes sense.
David Hayden
Related Posts: