title: Linq with Left Join on SubQuery containing Count
body: <p>I'm having difficulty translating sql to linq syntax.  </p>
  
  <p>I have 2 tables (Category and CategoryListing) which reference each other with CategoryID. I need to get a list of all the CategoryID in Category Table and the Count of CategoryID for all corresponding matches in the CategoryListing table. If a CategoryID is not present in CategoryListing, then the CategoryID should still be returned - but with a frequency of 0.</p>
  
  <p>The following sql query demonstrates expected results:</p>
  
  <pre><code>SELECT c.CategoryID, COALESCE(cl.frequency, 0) as frequency
  FROM Category c
  LEFT JOIN (
      SELECT cl.CategoryID, COUNT(cl.CategoryID) as frequency 
      FROM CategoryListing cl
      GROUP BY cl.CategoryID
  ) as cl
  ON c.CategoryID = cl.CategoryID
  WHERE c.GuideID = 1
  </code></pre>