Linq2SQl eager load with multiple DataLoadOptions

Question

I like to fetch the data with eager-loading using Linq2SQL. The code is similar as :

       DataLoadOptions options = new DataLoadOptions();

       options.LoadWith<Product>(c => c.ProductCompanies);

       options.LoadWith<Product>(c => c.OrderDetails);

       db.LoadOptions = options;

       IEnumerable<Product> products = db.Products.ToList<Product>();

I check it generated more than 1 SQL query as I expected. Actually it only do eager-loading with Product and OrderDetails, and the ProductCompany is queried one by one. Did I do anything wrong here? Or it is a Linq2SQL issue? Do we have any workaround?

Thanks a lot!

Update: I check the sql from SQL Profiler. I found both Leppie and Ian are correct. They are bounded in one transaction. But when I set it as lazy load, it opened multiple connection.

Answer 1

I hit this issue in some code too, and after much experimenting and googling it looks like LINQ can only join across a single one-to-many relationship from each table : if you try to specify more than one to pre-load it just (randomly?) picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)

Other people have posted this too, for example

http://codebetter.com/blogs/david.hayden/archive/2007/08/06/linq-to-sql-query-tuning-appears-to-break-down-in-more-advanced-scenarios.aspx