linq - Linq2Entities Equivalent Query for Parent/Child Relationship, With All Parents and Children, Filtering/Ordering Children

Question

So the question is ridiculously long, so let's go to the code. What's the linq2entities equivalent of the following Sql, given entities (tables) that look like:

Parent
---
parent_id
parent_field1

Child
--
child_id
parent_id
child_field1
child_field2

The sql:

select p.*, c.* 
from parent p 
inner join p on 
  p.parent_id = child.parent_id
where
  c.child_field1 = some_appropriate_value
order by
  p.parent_field1
  c.child_field2

L2E let's you do .include() and that seems like the appropriate place to stick the ordering and filtering for the child, but the include method doesn't accept an expression (why not!?). So, I'm guessing this can't be done right now, because that's what a lot of articles say, but they're old, and I'm wondering if it's possible with EF6.

Also, I don't have access to the context, so I need the lambda-syntax version.

I am looking for a resultant object hierarchy that looks like:

Parent1
  |
  +-- ChildrenOfParent1
  |
Parent2
  |
  +-- ChildrenOfParent2

and so forth. The list would be end up being an IEnumerable. If one iterated over that list, they could get the .Children property of each parent in that list.

Ideally (and I'm dreaming here, I think), is that the overall size of the result list could be limited. For example, if there are three parents, each with 10 children, for a total of 33 (30 children + 3 parents) entities, I could limit the total list to some arbitrary value, say 13, and in this case that would limit the result set to the first parent, with all its children, and the second parent, with only one of its children (13 total entities). I'm guessing all of this would have to be done manually in code, which is disappointing because it can be done quite easily in SQL.

Answer 1

when you get a query from db using entityframewrok to fetch parents, parent's fields are fetched in single query. now you have a result set like this:

var parentsQuery = db.Parents.ToList();

then, if you have a foreign key on parent, entityframework creates a navigation property on parent to access to corresponding entity (for example Child table).

in this case, when you use this navigation property from parent entities which already have been fetched, to get childs, entityframework creates another connection to sql server per parent.

for example if count of parentsQueryis 15, by following query entityframework creates 15 another connection, and get 15 another query:

var Childs = parentsQuery.SelectMany(u => u.NavigationProperty_Childs).ToList();

in these cases you can use include to prevent extra connections to fetch all childs with its parent, when you are trying to get parents in single query, like this:

var ParentIncludeChildsQuery = db.Parents.Include("Childs").ToList();

then by following Query, entityframework doesn't create any connection and doesn't get any query again :

var Childs = ParentIncludeChildsQuery.SelectMany(u => u.NavigationProperty_Childs).ToList();

but, you can't create any condition and constraint using include, you can check any constraint or conditions after include using Where, Join, Contains and so forth, like this:

var Childs = ParentIncludeChildsQuery.SelectMany(u => u.NavigationProperty_Childs
    .Where(t => t.child_field1 = some_appropriate_value)).ToList();

but by this query, all child have been fetched from database before

the better way to acheieve equivalent sql query is :

     var query = parent.Join(child,
                p => p.ID
                c => c.ParentID
                (p, c) => new { Parent = p, Child = c })
            .Where(u => u.Child.child_field1 == some_appropriate_value)
            .OrderBy(u => u.Parent.parent_field1)
            .ThenBy(u => u.Child.child_field2)
            .ToList();