title: select specific columns when using include statement with entity framework
body: <p>When I need a hierarchal (parent-child) relationship, I typically use the Include statement in my EF query.</p>
  
  <p>Example:</p>
  
  <pre><code>DbContext.Customers.Include("Projects");
  </code></pre>
  
  <p>This is fine, but the Customers and Projects entities always brings back all the columns. </p>
  
  <p>I know that the below query will bring back specific columns in the parent table, but I'm also trying to bring back only specific columns in the child table. If I use the intellisense on the Projects it is obviously a collection and does not give specific properties to select.</p>
  
  <pre><code>from c in Customers
  let Projects = c.Projects.Where (p =&gt; p.Notes != null)
  where Projects.Any()
  select new
  {
      c.UserName,
      Projects
  }
  </code></pre>
  
  <p>I tried refining the query to the below code, but as you can see, the Projects entity is a <strong>child</strong> entity of the Customers and therefore, does not have a specific column to select in the query. It obviously is a collection.</p>
  
  <p>Is there a way to bring back just <strong>specific columns</strong> in each of the entities when using an Include in your query?</p>
  
  <p>Note that my YeagerTechDB.ViewModels.Customers model is made up of all columns that reside in the Customer and Project entities.</p>
  
  <pre><code>public List&lt;YeagerTechDB.ViewModels.Customers&gt; GetCustomerProjects()
          {
              try
              {
                  using (YeagerTech DbContext = new YeagerTech())
                  {
                      var customer = DbContext.Customers.Include("Projects").Select(s =&gt;
                          new YeagerTechDB.ViewModels.Customers()
                          {
                              CustomerID = s.CustomerID,
                              ProjectID = s.ProjectID,
                              UserName = s.UserName,
                              Name = s.Projects.,
                          });
  
                       return customer.ToList();
                  }
              }
              catch (Exception ex)
              {
                  throw ex;
              }
          }
  </code></pre>
  
  <p>ANSWER #1 FOR 1 CHILD ENTITY</p>
  
  <pre><code>from c in Customers
  let Projects = c.Projects.Where (p =&gt; p.Notes != null)
  where Projects.Any()
  select new
  {
      c.UserName,
      Projects
  }
  </code></pre>
  
  <p>ANSWER #2 FOR 2 CHILD ENTITIES</p>
  
  <pre><code>from c in Customers
  let highValueP =
      from p in c.Projects
      where p.Quote != null
      select new { p.ProjectID, p.Name, p.Quote }
  where highValueP.Any()
  from p in Projects
  let highValuet =
      from t in p.TimeTrackings
      where t.Notes != null
      select new { t.ProjectID, t.Notes }
  where highValuet.Any()
  select new 
  {
      c.CustomerID,
      Projects = highValueP,
      TimeTrackings = highValuet
  }
  </code></pre>
  
  <p>Edit #3
  <img src="https://i.stack.imgur.com/zrkY6.png" alt="enter image description here"></p>