Entity framework linq query Include multiple children entities

Question

This may be a really elementry question but whats a nice way to include multiple children entities when writing a query that spans THREE levels  or more    i e  I have 4 tables  Company  Employee  Employee Car and Employee Country  Company has a 1 m relationship with Employee   Employee has a 1 m relationship with both Employee Car and Employee Country   If i want to write a query that returns the data from all 4 the tables  I am currently writing   Company company   context Companies                           Include  Employee Employee Car                             Include  Employee Employee Country                             FirstOrDefault c   gt  c Id    companyID     There has to be a more elegant way  This is long winded and generates horrendous SQL  I am using EF4 with VS 2010

User · Answer

Might be it will help someone  4 level and 2 child s on each level Library Include a   gt  a Library Select b   gt  b Library Select c   gt  c Library                 Include d  gt d Book                Include g   gt  g Library Select h  gt g Book                Include j   gt  j Library Select k   gt  k Library Select l  gt l Book

User · Answer

EF 4 1 to EF 6 There is a strongly typed  Include which allows the required depth of eager loading to be specified by providing Select expressions to the appropriate depth  using System Data Entity     NB   var company   context Companies                       Include co   gt  co Employees Select emp   gt  emp Employee Car                         Include co   gt  co Employees Select emp   gt  emp Employee Country                         FirstOrDefault co   gt  co companyID    companyID    The Sql generated is by no means intuitive  but seems performant enough  I ve put a small example on GitHub here EF Core EF Core has a new extension method   ThenInclude    although the syntax is slightly different  var company   context Companies                       Include co   gt  co Employees                              ThenInclude emp   gt  emp Employee Car                        Include co   gt  co Employees                              ThenInclude emp   gt  emp Employee Country   With some notes  As per above  Employees Employee Car and Employees Employee Country   if you need to include 2 or more child properties of an intermediate child collection  you ll need to repeat the  Include navigation for the collection for each child of the collection  As per the docs  I would keep the extra  indent  in the  ThenInclude to preserve your sanity

User · Answer

Use extension methods  Replace NameOfContext with the name of your object context   public static class Extensions     public static IQueryable lt Company gt  CompleteCompanies this NameOfContext context            return context Companies               Include  Employee Employee Car                 Include  Employee Employee Country                  public static Company CompanyById this NameOfContext context  int companyID            return context Companies               Include  Employee Employee Car                 Include  Employee Employee Country                 FirstOrDefault c   gt  c Id    companyID                 Then your code becomes        Company company              context CompleteCompanies   FirstOrDefault c   gt  c Id    companyID           or if you want even more      Company company              context CompanyById companyID

User · Answer

You might find this article of interest which is available at codeplex com    Improving Entity Framework Query Performance Using Graph-Based Querying    The article presents a new way of expressing queries that span multiple tables in the form of declarative graph shapes    Moreover  the article contains a thorough performance comparison of this new approach with EF queries  This analysis shows that GBQ quickly outperforms EF queries

User · Answer

How do you construct a LINQ to Entities query to load child objects directly  instead of calling a Reference property or Load    There is no other way - except implementing lazy loading   Or manual loading      myobj   context MyObjects First    myobj ChildA Load    myobj ChildB Load

[linq] Entity framework linq query Include() multiple children entities

Examples related to linq

Examples related to entity-framework

Examples related to lazy-loading