title: Queries generated by group by vs group join
body: <p>I have the following group by linq statement</p>
  
  <pre><code>from c in Categories
  join p in Products on c equals p.Category into ps
  select new { Category = new {c.CategoryID, c.CategoryName}, Products = ps };
  </code></pre>
  
  <p>However this generates the following left outer join query and returns all categories even if there are no products associated.</p>
  
  <pre><code>SELECT [t0].[CategoryID], [t0].[CategoryName], [t1].[ProductID], [t1].[ProductName],     [t1].[SupplierID], [t1].[CategoryID] AS [CategoryID2], [t1].[QuantityPerUnit],   [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], (
      SELECT COUNT(*)
      FROM [Products] AS [t2]
      WHERE [t0].[CategoryID] = [t2].[CategoryID]
      ) AS [value]
  FROM [Categories] AS [t0]
  LEFT OUTER JOIN [Products] AS [t1] ON [t0].[CategoryID] = [t1].[CategoryID]
  ORDER BY [t0].[CategoryID], [t1].[ProductID]
  </code></pre>
  
  <p>What I really want is to return only those categories that have associated products. But if I re-write the linq query like so:</p>
  
  <pre><code>from c in Categories
  join p in Products on c equals p.Category
  group p by new {c.CategoryID, c.CategoryName} into ps
  select new { Category = ps.Key, Products = ps };
  </code></pre>
  
  <p>This gives me the desired result but <b>a query is generated for <i>each</i> category</b>:</p>
  
  <pre><code>SELECT [t0].[CategoryID], [t0].[CategoryName]
  FROM [Categories] AS [t0]
  INNER JOIN [Products] AS [t1] ON [t0].[CategoryID] = [t1].[CategoryID]
  GROUP BY [t0].[CategoryID], [t0].[CategoryName]
  GO
  
  -- Region Parameters
  DECLARE @x1 Int SET @x1 = 1
  DECLARE @x2 NVarChar(9) SET @x2 = 'Beverages'
  -- EndRegion
  SELECT [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID],  [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued]
  FROM [Categories] AS [t0]
  INNER JOIN [Products] AS [t1] ON [t0].[CategoryID] = [t1].[CategoryID]
  WHERE (@x1 = [t0].[CategoryID]) AND (@x2 = [t0].[CategoryName])
  GO
  
  -- Region Parameters
  DECLARE @x1 Int SET @x1 = 2
  DECLARE @x2 NVarChar(10) SET @x2 = 'Condiments'
  -- EndRegion
  SELECT [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued]
  FROM [Categories] AS [t0]
  INNER JOIN [Products] AS [t1] ON [t0].[CategoryID] = [t1].[CategoryID]
  WHERE (@x1 = [t0].[CategoryID]) AND (@x2 = [t0].[CategoryName])
  GO
  
  ...
  </code></pre>
  
  <p><strong>Is there a way to do the equivalent of a inner join and group by and still only produce a single query like the group join?</strong></p>