title: L2E: GroupBy always gets translated into Distincts, never Group By
body: <p>How can I get the Linq-to-Entities provider to truly perform a <code>GROUP BY</code>? No matter what I do, it always generates SQL that is far slower than an actual <code>GROUP BY</code>. For example:</p>
  
  <pre><code>var foo = (from x in Context.AccountQuantities
    where x.AccountID == 27777
    group x by x.StartDate
    into groups
    select new
      {
        groups.FirstOrDefault().StartDate,
        Quantity = groups.Max(y =&gt; y.Quantity)
      }).ToList();
  </code></pre>
  
  <p>translates to:</p>
  
  <pre><code>SELECT 
  1 AS [C1], 
  [Project4].[C1] AS [C2], 
  [Project4].[C2] AS [C3]
  FROM ( SELECT 
      [Project3].[C1] AS [C1], 
      (SELECT 
          MAX([Extent3].[Quantity]) AS [A1]
          FROM [dbo].[AccountQuantities] AS [Extent3]
          WHERE (27777 = [Extent3].[AccountID]) AND ([Project3].[StartDate] = [Extent3].[StartDate])) AS [C2]
      FROM ( SELECT 
          [Distinct1].[StartDate] AS [StartDate], 
          (SELECT TOP (1) 
              [Extent2].[StartDate] AS [StartDate]
              FROM [dbo].[AccountQuantities] AS [Extent2]
              WHERE (27777 = [Extent2].[AccountID]) AND ([Distinct1].[StartDate] = [Extent2].[StartDate])) AS [C1]
          FROM ( SELECT DISTINCT 
              [Extent1].[StartDate] AS [StartDate]
              FROM [dbo].[AccountQuantities] AS [Extent1]
              WHERE 27777 = [Extent1].[AccountID]
          )  AS [Distinct1]
      )  AS [Project3]
  )  AS [Project4]
  </code></pre>
  
  <p>How can I get it to execute this instead?</p>
  
  <pre><code>SELECT
  AccountQuantities.StartDate,
  MAX(AccountQuantities.Quantity)
  FROM AccountQuantities
  WHERE AccountID=27777
  GROUP BY StartDate
  </code></pre>
  
  <p>Again, note the lack of ANY <code>GROUP BY</code> in what's executed. I'm fine with EF optimizing things non-ideally but this is orders of magnitude slower, I cannot find any way to convince it to really do a <code>GROUP BY</code>, and is a major problem for us!</p>