Linq to Sql Force Order By

Question

Is there a way in Linq to Sql (.net 3.5) to specify that when you get the children of a particular record, to force the list of children to come back in a specific order?

For example, I have a list that has a "DisplayOrder" on it. When I ask my parent record/object for it's property that returns to me this list, I want Linq to Sql to retrieve for me the records, but ordered by "DisplayOrder".

Is it possible to do this without always writing something like:

MyObject.Children.OrderBy(c => c.DisplayOrder);

An idea off the top of my head is to create a partial class for my object and add an "OrderedChildren" property that encapsulates this call. Wondering though, if there isn't something I can specify in the dbml to force this.

Answer 1

The DataLoadOptions.AssociateWith() method will filter all objects for a certain relationship. This includes Where(), OrderBy(), ThenBy(), OrderByDescending(), ThenByDescending(), and Take().

You can use this within a partial method in your DataContext like the following:

partial class ExampleDataContext
{
    partial void OnCreated()
    {
        DataLoadOptions dlo = new DataLoadOptions();
        dlo.AssociateWith<MyObject>(i => i.Children.OrderBy(c => c.DisplayOrder));
        LoadOptions = dlo;
    }
}

You'll want to make sure the LoadOptions are never overwritten prior to running other queries.