LINQ-to-SQL: sorting related entity set at load time

Imagine I have two tables ShowTimes and Movies. Each Movie can have a list of related ShowTimes. Say the ShowTimes table contains a DateTime field called StartTime. The ShowTimes can appear i开发者_如何学Pythonn the table in any order (the theatre manager can add new showings at any time).

Therefore, the LINQ-to-SQL Movie object will contain a

public EntitySet<ShowTime> ShowTimes;

field, which will be loaded when it is first accessed.

Is there any way to control the sort order of ShowTimes so that it will be sorted by StartTime when it is loaded? Or must I sort it myself before I use it? Can I control the “natural order” of the ShowTimes table – by default it is sorted by primary key (an integer, in my case), can I specify that it is to be sorted by StartTime?

You can set a default sort order for child entities with DataLoadOptions.AssociateWith().

For example:

DataContext context = new DataContext();

DataLoadOptions options = new DataLoadOptions();

options.AssociateWith<Movie>(movie => movie.ShowTimes
                    .OrderBy(showtime => showtime.StartTime));

context.LoadOptions = options;

// do your queries here, after the LoadOptions has been set

Note that LoadOptions can only be set once on a DataContext.

Also note that DataLoadOptions can be used to eager load, child entities.

You could bypass lazy loading by using .ToList(), .ToArray(), or .ToDictionary().

IEnumerable<ShowTime> ShowTimes = 
     (from showtime in DataContext.ShowTimes
     order by showtime.StartTime
     select showtime).ToList();