Constraints With LoadWith when Loading multiple 1:n relationships

This problem totally came as a surprise when one of our pages in production was taking too long time to load. Obviously to trouble shoot the problem, I opened up SQL profiler, started looking at the various SQL statements being sent to the database. I was surprised to see that LoadWith operator was not working as advertised when eager loading more than one, one to many relationship. To demonstrate the problem, let's start with a bare bone example where I eager load Orders for the customers in the city of London.

In the above example, I am using LoadWith operator to eagerly load Order for my customer. Looking at the query sent to the database, you can confirm that there was not extra query sent to the database to fetch the orders, it came along with the customers.

When I change the DataLoadOptions slightly to also bring OrderDetails, for each Order when you are loading Customer, the query plan changes radically. Linq to SQL first brings all the customers that match the criteria and there are no other joins added to the query to attempt to bring orders and OrderDetails for all those customers. When you iterate through the customer, for each customer in the list, Linq to SQL makes a separate database call to fetch all the Orders for that customer bringing along OrderDetails tied to those orders. I am not sure what criteria Linq to SQL uses to determine which table it needs to bring individually and which tables needs to be brought together in one query. Example below shows separate database calls made for each customer to fetch its orders and order details.

In the above example, you can see that I have added another option in my DataLoadOptions to load OrderDetails for my Order as well. When we run the example above, a separate database call is made to fetch orders and OrderDetails for each Customer as shown by the SQL capture.

The obvious question is, this is not the advertised behavior of DataLoadOptions which is supposed to eagerly load all the Child Collections. In a side project that I am working on that uses NHibernate, I have not come across this issue. May be readers can shed some light on why this is happening and if someone has found a work around to this solution, please post the solution so that others can benefit. 

One bad solution that I have done to fix the problem temporarily is, turn off deferred loading and run separate queries to fetch customers, orders and orderdetails in 3 database calls and assign their collections manually on the client side. This way I had more control of how many calls were being made. However the solution is not very clean but at the end of the day, it does the job.