title: Entity Framework: Left Join with List Result
body: <p>I'm trying to optimize my EF queries.  I have an entity called Employee.  Each employee has a list of tools.  Ultimately, I'm trying to get a list of employees with their tools that are NOT broken.  When running my query, I can see that TWO calls are made to the server: one for the employee entities and one for the tool list.  Again, I'm trying to optimize the query, so the server is hit for a query only once.  How can I do this?</p>
  
  <p>I've been exploring with LINQ's join and how to create a LEFT JOIN, but the query is still not optimized.</p>
  
  <p>In my first code block here, the result is what I want, but -- again -- there are two hits to the server.</p>
  
  <pre><code>public class Employee
  {
      public int EmployeeId { get; set; }
      public List&lt;Tool&gt; Tools { get; set; } = new List&lt;Tool&gt;();
      ...
  }
  
  public class Tool
  {
      public int ToolId { get; set; }
      public bool IsBroken { get; set; } = false;
  
      public Employee Employee { get; set; }
      public int EmployeeId { get; set; }
      ...
  }
  </code></pre>
  
  <pre><code>var x = (from e in db.Employees.Include(e =&gt; e.Tools)
           select new Employee()
           {
               EmployeeId = e.EmployeeId,
               Tools = e.Tools.Where(t =&gt; !t.IsBroken).ToList()
           }).ToList();
  </code></pre>
  
  <p>This second code block pseudoly mimics what I'm trying to accomplish.  However, the GroupBy(...) is being evaluated locally on the client machine.</p>
  
  <pre><code>(from e in db.Employees
   join t in db.Tools.GroupBy(tool =&gt; tool.EmployeeId) on e.EmployeeId equals t.Key into empTool
   from et in empTool.DefaultIfEmpty()
   select new Employee()
   {
      EmployeeId = e.EmployeeId,
      Tools = et != null ? et.Where(t =&gt; !t.IsBroken).ToList() : null
   }).ToList();
  </code></pre>
  
  <p>Is there anyway that I can make ONE call to the server as well as not having my GroupBy() evaluate locally and have it return a list of employees with a filtered tool list with tools that are not broken?  Thank you.</p>