title: Entity Framework Linq, Left Join and group with SUM and Count
body: <p>I need a little help in converting SQL to Linq.  It's pretty straight forward in MySQL...</p>
  
  <pre><code>Table: customers
  ID  Name    
  1   Bill
  2   John
  
  Table: purchases
  ID  CustomerID  CompletedTransaction
  1   1           False
  2   2           True
  3   1           True
  4   1           True
  
  
      SELECT  c.ID
          c.Name,
          COUNT(p.ID) AS TotalPurchases,
          SUM(CASE WHEN p.CompletedTransaction = TRUE THEN 1 ELSE 0 END) AS                       TotalCompleted
      FROM customers c
      LEFT JOIN purchases p ON c.ID = p.CustomerID
      GROUP BY c.ID
  
  Expected Result:
  1, Bill, 3, 2
  2, John, 1, 1
  </code></pre>
  
  <p>I've seen a few examples on how to implement a left join in Linq but I'm not sure how to include a SUM and Count into this. I've seen examples in Linq where the fields returned are selected from the group keys.  Does this mean that if I have more fields in the customers table such as address and other contact details which I'd like to return, I'd have to include them in the join to then be able to select them?  Hope this makes sense.  Appreciate any help or links that might point me in the right direction.</p>
  
  <p>Thanks</p>