Ordering Lazy Loaded Child Collections on the database

I have been working with OR mappers for more than 3 years and had the luxury to work with many different ones such as Wilson, NHibernate, Sonic and Linq. One of the constraints that I have encountered in most ormappers is, there is no clean way to define how to sort child collections based on certain column. For example if I have a customer instance in my hand and I want to get access to its Orders, I can simply navigate to Orders property of the customer. What if I want those orders to be sorted by ShipCity. Well in Linq queries you can apply OrderBy operator on Orders collection for the customer. But does that order by operation gets executed on SQL server. The answer is no. As soon as we access the Orders property of the customer, Linq to SQL fetches all the orders for the customer in memory. From there on any operations you perform will get executed in memory. Below is an example that confirms the behavior.

In the screen shot above, we can see that our order by clause has fixed the ordering issue on our child collection orders and our orders are sorted by ship city. However the ordering is performed in memory. As I mentioned earlier, as soon as we access Orders collection the orders are fetched from the database and brought in memory. You don't get a chance to give any hints to retrieve the orders from the database in a specific order such as ship city. However Linq to SQL offers DataLoadOptions which has AsscoiateWith method that takes in a generic type and allows you to put your sorting requirement in the form a lambda statement. Below is the code that shows how to perform ordering on child collections on the database server.

In the code above, I am creating an instance of DataLoadOptions and passing in the lambda expression to AssociateWith operator. According to msdn documentation you can use Associate with operator to filter any child collection. You can use AssociateWith operator to apply any kind of transformation that Linq to SQL query engine will allow which includes sorting the child collections as well. From the SQL capture, you can confirm that indeed the sorting got performed on SQL server and are results are printed in the correct sort order.