w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Linq2Entities Equivalent Query for Parent/Child Relationship, With All Parents and Children, Filtering/Ordering Children

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();




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.