Friday, September 6, 2013

How can I conditionally add where clauses and filter children in a single linq query?

How can I conditionally add where clauses and filter children in a single
linq query?

I'm using entity framework and building up a linq query so the query is
executed at the database to minimize data coming back and the query can
have some search criteria which is optional and some ordering which is
done every time. I am working with parents and children (the mummy and
daddy type). The filter I am trying to implement is for age of the
children.
So if I have some data like so...
parent 1
- child[0].Age = 5
- child[1].Age = 10
parent 2
- child[0].Age = 7
- child[1].Age = 23
...and I specify a minimum age of 8, my intended result to display is...
parent 1
- child[1].Age = 10
parent 2
- child[1].Age = 23
...and if I specify a minimum age of 15 I intend to display...
parent 2
- child[1].Age = 23
I can re-create my expected result with this horrible query (which I
assume is actually doing more than one query):
var parents = context.Parents;
if(minimumChildAge.HasValue)
{
parents = parents.Where(parent => parent.Children.Any(child =>
child.Age >= minimumChildAge.Value));
foreach(var parent in parents)
{
parent.Children = parent.Children.Where(child =>
child.minimumChildAge.Value >= mimumumChildAge);
}
}
parents = parents.OrderBy(x => x.ParentId).Take(50);
So I tried the other method instead...
var query = from parent in context.Parents
select parent;
if (minimumChildAge.HasValue)
query = from parent in query
join child in context.Children
on parent.ParentId equals child.ParentId
where child.Age >= minimumChildAge.Value
select parent;
query = query.OrderBy(x => x.ParentId).Take(50);
When I run this in linqpad the query generated looks good. So my question...
Is this the correct way of doing this? Is there a better way? It seems a
bit funny that if I now specified a maximum age that I would be writing
the same joins and hoping that entity framework works it out. In addition,
how does this impact lazy loading? I expect only the children which match
the criteria to be returned. So when I do parent.Children does entity
framework know that it just queried these and its working on a filtered
collection?

No comments:

Post a Comment