title: LINQ count in group from join
body: <p>I have a LINQ statement I am trying to get right, so maybe going about this all wrong. My objective is to query a table and join in another table to get counts.</p>
  
  <p><strong>Places</strong><br>
  ID, Display</p>
  
  <p><strong>ProfilePlaces</strong><br>
  ID, PlaceID, Talk, Hear</p>
  
  <p>Basically <code>Places</code> have <code>ProfilePlaces</code> in a one to many relationship. I want to get the number <code>SUM</code> of <code>ProfilePlaces</code> that have <code>Talk</code>and <code>Hear</code>. <code>Talk</code>and <code>Hear</code> are <code>bit</code> fields.</p>
  
  <p>The following gives me a unique list of <code>Places</code>, so I need to add in the <code>Talk</code>and <code>Hear</code> counts.</p>
  
  <pre><code>var counts = from p in db.Places
               join pp in db.ProfilePlaces on p.ID equals pp.PlaceID
               group new { Place = p } by p.Display;
  </code></pre>
  
  <p>I thought something like this, but not having any luck</p>
  
  <pre><code>var counts = from p in db.Places
               join pp in db.ProfilePlaces on p.ID equals pp.PlaceID
               group new { Place = p, 
                           Talk = pp.Count(t =&gt; t.Talk == true),
                           Hear = pp.Count(t =&gt; t.Hear == true) 
                         } by p.Display;
  </code></pre>
  
  <p>Thanks for any help.</p>