Hurrah! I get to use DLinq, therefore making me a
DLinquent.
[n.b. I googled for "dlinquent dlinq" to see if anyone else has thought of that pun before, and there were no results, which means I thought of it first, hence I am officially witty. Others have used the term dlinquent, but in a different context, thus they cannot justifiably diminish my wittiness. If anyone else tries to pass off "DLinquent" as their own, please refer them to this blog post. Thanks.]
OK, here goes. Let me select a couple of hundred rows from a table and do something with them:
var musicians = from t in DB.Musicians
select t
foreach (Musician musician in musicians)
{
// Do a thing ...
}
Cool. Now lets do some other stuff:
foreach (Musician musician in musicians)
{
foreach (Album album in musician.Albums)
{
// Do a thing ...
}
}
Wow. Even more cool. Actually, hang on, that took ages. Let's try that again with a stopwatch. Hmm. 4 seconds, and there are only 200 musicians, and the database is on the local machine.
Time for SQL profiler, and stepping through the code...
OK, so the select doesn't actually
run the select against the database, it just defines it. The foreach runs a select statement. So does the next foreach. Back to the top foreach again. Yup, another select against the database. And another, and another, and, er, hang on, it's
fetching data one row at a time. I think it would be quicker to get it all at the beginning, and do the stuff when it's all been fetched.
var musicians = from t in DB.Musicians
select t
Array<Musician> musiciansArray = musicians.ToArray();
foreach (Musician musician in musiciansArray)
{
foreach (Album album in musician.Albums)
{
// Do a thing ...
}
}
That's a bit faster. It's fetched all the musicians when it built the array. The albums are still being fetched one at a time though. [Please excuse me while I google...]
Aha! Right, try again.
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith(m => m.Albums);
DB.LoadOptions = loadOptions;
var musicians = from t in DB.Musicians
select t
Array<Musician> musiciansArray = musicians.ToArray();
foreach (Musician musician in musiciansArray)
{
foreach (Album album in musician.Albums)
{
// Do a thing ...
}
}
Brilliant. Now the albums are loaded at the same time as the musicians, right up front. Down to two hundredths of a second.
