In the last post:
Take the example in the forum post that discusses grabbing the Customers from the Northwind Database and prefetching their Orders and OrderDetails at the same time:
which you could code like the following:
using (NorthwindDataContext context = new NorthwindDataContext())
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Customer>(c => c.Orders);
options.LoadWith<Order>(o => o.Order_Details);
IEnumerable<Customer> customers = context.Customers.ToList<Customer>();
Opening up SQL Server Profiler shows a rather ugly set of queries ( 40+ queries in total ) that breakdown as:
1 Query to First Retrieve all the Customers in Northwind
40+ Queries – 1 Query for each Customer to get its Orders and OrderDetails.
Again, you have that startling number of database roundtrips in LINQ To SQL that typically you want to avoid if possible to improve performance.
Since Frans mentioned it :), I wrote similar code to do the same thing using LLBLGen Pro and Self-Servicing Entities:
CustomerCollection customers = new CustomerCollection();
IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.CustomerEntity);
which resulted in only 3 queries ( roundtrips ) that first grabbed all the Customers, then all the Orders, and then all the OrderDetails. This is more inline with what I was expecting from LINQ To SQL.
I couldn’t find a way to tweak LINQ To SQL to reduce the number of queries, but maybe Scott Guthrie can come up with a more practical solution in his next installment of LINQ To SQL. This is just something to keep in mind when using the current version of LINQ To SQL. Always review the database communication with SQL Server Profiler to determine what is actually being generated.
by David Hayden