title: LINQ group by with join and count occurence of the same item
body: <p>I have two tables, one for the <strong>categories</strong> and one for the <strong>products</strong></p>
  
  <p>They are defined in the following way:</p>
  
  <pre><code>public class Product
  {
      public int ProductID{get;set;}
      public string ProductName{get;set;}
      public int CompanyID{get;set;}
      public int CategoryID{get;set;}
  }
  
  public class Category
  {
      public int CategoryID{get;set;}
      public string CategoryName{get;set;}
  }
  </code></pre>
  
  <p>The two tables are joined on CategoryID</p>
  
  <p><strong>I'm trying to get the list of categories with the count of how many companies produce products in that category</strong></p>
  
  <p>So it's not the count of how many products by category, but how many distinct companies produce product(s) for each category.</p>
  
  <p>I've tried with this query but I'm not able to get it right:</p>
  
  <pre><code>var query =
      from c in categories
      join p in products on c.CategoryID equals p.CategoryID
      group c by new {c.CategoryName,p.CompanyID} into cg
      select new
      {
          CatName = cg.Key.CategoryName,
          Count = cg.Count()
      };
  </code></pre>
  
  <p>I've prepared a fiddle ready with sample data here:</p>
  
  <p><a href="https://dotnetfiddle.net/WHwNFk" rel="nofollow noreferrer">https://dotnetfiddle.net/WHwNFk</a></p>
  
  <p>The result in the fiddle should be: A=2, B=2, C=2 (so for each category there are 2 different companies that make products in that category)</p>