How do I limit the number of results back in LinqToSql?

Question

Hi folks,

I'm used to loading some children results (for a parent result) using the LoadWith syntax. Works great. Is there a way I can limit these LoadWith results to the most recent 5 or something?

I've got some pseduo code with some inline comments to help explain what I'm trying to do...

Eg.

IList<Parent> results;

using (DataBaseContext db = new MyDb())
{
    var dlo = new DataLoadOptions();
    dlo.LoadWith<Parent>(x => x.Child1);  // We only want the most recent 10.
    dlo.LoadWith<Parent>(x => x.Child2);  // All of these...
    dlo.LoadWith<Parent>(x => x.Child3);  // Only the most recent 1.
    db.LoadOptions = dlo;

    results = (from p in Parent
               orderby p.Id descending
               select p).Take(5).ToList();
}

Cheers :)

Answer 1

This should work, assuming you set the sort order to what makes sense. (DataLoadOptions.AssociateWith() Reference)

IList<Parent> results;

using (DataBaseContext db = new MyDb())
{
    var dlo = new DataLoadOptions();
    dlo.LoadWith<Parent>(x => x.Child1);  // We only want the most recent 10.
    dlo.AssociateWith<Parent>(x => x.Child1.OrderByDescending(c => c.Date).Take(10));
    dlo.LoadWith<Parent>(x => x.Child2);  // All of these...
    dlo.LoadWith<Parent>(x => x.Child3);  // Only the most recent 1.
    dlo.AssociateWith<Parent>(x => x.Child3.OrderByDescending(c => c.Date).Take(1));
    db.LoadOptions = dlo;

    results = (from p in Parent
               orderby p.Id descending
               select p).Take(5).ToList();
}

EDIT BY Pure Krome

Please note (anyone who reads this) that if you use the AssociateWith method, you HAVE TO preceed it with a LoadWith. Notice how we LoadWith(child1) and the next line we AssociateWith(..some funky-ass lambda) ?? That's good -> if u forget to put in the LoadWith before the AssociateWith, no sql will be generated and nothing will be returned for that child.