I have been using SQL to Linq for a while. It is a fairly straight forward framework for retrieving data for my data access layer. The Linq syntax makes it very easy to read while allowing for complex expressions.
Unfortunately, the performance may be really bad in some cases. This is typically because of lazy loading where a round trip is required per row because associated data are loaded. The typical example from the Northwind database is a Customer with multiple Orders. All the Customers are retrieved in one go, however for each customer the associated orders are retrieved in one SQL request. This will generate one SQL request for the customers and then n for the orders. This is hopeless. Fortunately,
Microsoft provided us with the DataLoadOptions.LoadWith method. In this case, the following statements solves the problem:
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith(c => c.Orders);
db.LoadOptions = dlo;
But what happens if each order has a set of associations? Lets say each order has a collection of OrderItems. You will end up with a round trip to the database for each order. Each round trip will retrieve the associated OrderItems.
So what do you do? You may try to add a second LoadWith statement... but it turns out that this is not supported by Linq to SQL. So if you add the second LoadWith, the framework will do what you tell it. However, it will use multiple SQL statements. So what is the point then?
In order to fix this, use this code:
var orderItems= db.OrderItems.Where(oi => orders.Select(o => o.ID).Contains(oi.OrderID)).ToList();
foreach (var o in orders)
{
var oClosure = o;
oClosure.OrderItems.SetSource
(orderItems.Where(oi => oi.OrderItemID ==
cClosure.ID));
}
So basically, by pre-fetching orderItems and manually setting the source by using SetSource, the number of round trips are reduced to two. Great? ;-)
