Eager Loading in Linq to SQL
.. is not quite as easy as it should be.
In LINQ to SQL, when you execute a query, it seems like dependent properties (i.e. properties that are objects formed based on relationships between tables, think Customer-->Order)... are lazy loaded by default.
Now, I do believe that is probably best - since in general, when you do a query over one table, it will be hard to know in a complex graph which tables to eager-load and which tables and rows to lazy load.
The solution is to use the DataLoadOptions class to specify which dependent properties you want to "eager-load".
So, say, you are doing the following query:
//sets up eager loading on most of the dependent properties
data.EagerLoad();
//grab the raw data we want... will group later
var oddsDataRaw = from odds in data.OddDTOs
where odds.GameDTO.GameDate >= startDate
&& odds.GameDTO.GameDate <= endDate
&& odds.GameDTO.HomeTeam.TeamSeasons.Seasons.Leagues.Initials == sport
select odds;
//convert the data into a list, then group it
var oddsData = (from o in (oddsDataRaw.ToArray())
group o by o.GameID).ToArray();
Of course, you might wonder what the EagerLoad routine does... :). Well, yes, there is no EagerLoad() - I wrote it as an extension method:
public static void EagerLoad(this WTDataContext dataContext)
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<OddDTO>(o => o.GameDTO);
options.LoadWith<OddDTO>(o => o.OddsMakers);
options.LoadWith<GameDTO>(g => g.AwayTeam);
options.LoadWith<GameDTO>(g => g.HomeTeam);
options.LoadWith<Team>(t => t.TeamSeasons);
options.LoadWith<TeamSeason>(ts => ts.Seasons);
options.LoadWith<Season>(s => s.Leagues);
dataContext.LoadOptions = options;
}
Now, prior to doing this, reading stuff from my object graph that resulted from the initial query took a very long time. I am consolidating data here from about 6 normalized tables. Prior to setting up data options, the query and resulting mapping to my business objects took about 30 seconds. Given this runs behind a web page that summarizes stats for sports games, that isn't really going to be viable.
Calling the EagerLoad extension method allows the query to load up the related objects from the graph according to the specification that gets built with the LoadWith routine. Specifically, you call LoadWith by passing the parent object type as the generic parameter, then passing a Lambda that has the property of the said type to "eager load". In fact, LoadWith could have been called "EagerLoadThisClass" instead, and probably conveyed more meaning (but I digress).
Of course, it would be nice if there were a way to have a syntax that went like this:
dataContext.EagerLoad(Delegate someCode)
which would, of course, set up your options based on the content of the delegate (or some expression set representing the delegate).. but I will leave that as an "exercise to the reader" :)