title: Linq: query with three nested levels
body: <p>So I have three tables:</p>
  
  <pre><code>CREATE TABLE tblUser
  (
      [pkUserID] [int] IDENTITY(1,1) NOT NULL,
      [userName] [varchar](150) NULL,
      [fkCompanyID] [int] NOT NULL
  )
  
  CREATE TABLE tblCompany
  (
      [pkCompanyID] [int] IDENTITY(1,1) NOT NULL,
      [name] [varchar](255) NULL
  )
  
  CREATE TABLE tblSystem
  (
      [pkSystemID] [int] IDENTITY(1,1) NOT NULL,
      [systemName] [varchar](150) NULL,
      [fkCompanyID] [int] NULL
  )
  </code></pre>
  
  <p>These are my data transfer objects:</p>
  
  <pre><code>public class SystemDTO
  {
      public int pkSystemId { get; set; }
      public string Name { get; set; }
      public int? fkCompanyId { get; set; }
  }
  
  public class CompanyDTO
  {
      public int pkCompanyId { get; set; }
      public string Name { get; set; }
      public IEnumerable&lt;SystemDTO&gt; Systems { get; set; }
  }
  
  public class UserDTO
  {
      public int pkUserId { get; set; }
      public string Name { get; set; }
      public IEnumerable&lt;CompanyDTO&gt; Companies { get; set; }
  }
  </code></pre>
  
  <p>This is the Linq query I am trying to do:</p>
  
  <pre><code>var result= (
          from user in db.tblUsers
          select new UserDTO()
          {
              pkUserId=user.pkUserID,
              Name=user.realName,
              Companies=
                  (
                      from company in db.tblCompanies
                      where user.fkCompanyID==company.pkCompanyID
                      select new CompanyDTO()
                      {
                          pkCompanyId=company.pkCompanyID,
                          Name=company.name,
                          Systems=
                          (
                              from system in db.tblSystem
                              where system.fkCompanyId==company.pkCompanyId
                              select new SystemDTO()
                              {
                                  pkSystemId=system.pkSystemID,
                                  Name=system.systemName,
                                  fkCompanyId=system.fkCompanyID
                              }
                          )
                      }
                  )
          }
      ).ToList();
  </code></pre>
  
  <p>The problem with this query is that the most inner query</p>
  
  <pre><code>from system in db.tblSystem
  where system.fkCompanyId==company.pkCompanyId
  select new SystemDTO()
  {
      pkSystemId=system.pkSystemID,
      Name=system.systemName,
      fkCompanyId=system.fkCompanyID
  }
  </code></pre>
  
  <p>cause linq to translate the sql to one select per entity. I know that I can skip the select and loop the result and set the property. Like this:</p>
  
  <pre><code>var lsSystem= db.tblSystem.Select (s =&gt;new SystemDTO(){pkSystemId=s.pkSystemID,Name=s.systemName,fkCompanyId=s.fkCompanyID}).ToList();
  foreach (var user in result)
      {
          foreach (var company in user.Companies)
          {
              company.Systems=lsSystem.Where (a =&gt;a.fkCompanyId==company.pkCompanyId).ToList();
          }   
      }
  </code></pre>
  
  <p>This will cause linq to do two select and not one per entity. So now to my questions. Is there any another way of doing this? Can I populate the inner collection in another way?</p>
  
  <p>Any suggesting will be appreciated</p>
  
  <p><strong>EDIT</strong><br>
  A suggesting was to use loadoption. I can't find a loadoption between system and company. But I can include the loadoption between. Company and user like this:</p>
  
  <pre><code>var option=new DataLoadOptions();
  option.LoadWith&lt;tblCompany&gt;(a=&gt;a.fkCompanytblUsers);
  db.LoadOptions=option;
  </code></pre>
  
  <p>But this has no effect on the query it is still translated to many selects</p>
  
  <p><strong>EDIT2</strong></p>
  
  <p>As said in the answers comments the load options do not apply for this kinda linq query. </p>