title: Performance Issues with Sum on Group in Entity Framework Core
body: <p>I have this Linq query:</p>
  
  <pre><code>DataBase
      .GroupBy(data =&gt; new { data.X, data.Y }, (key, group) =&gt; new
      {
          key.X,
          key.Y,
          Somethings = group.Sum(x =&gt; x.Something),
  
          [Code shortened for brevity]
  
      })
      .OrderBy(row =&gt; row.X)
      .ThenBy(row =&gt; row.Y)
  </code></pre>
  
  <p>When using Linq-to-SQL it translates beautifully into the SQL query I was expecting, something like:</p>
  
  <pre><code>SELECT [t1].[x] AS [X], [t1].[y] AS [Y], [t1].[value] AS [Somethings], [...]
  FROM (
      SELECT SUM([t0].[something]) AS [value], [...], [t0].[x], [t0].[y]
      FROM [table] AS [t0]
      GROUP BY [t0].[x], [t0].[y]
      ) AS [t1]
  ORDER BY [t1].[x], [t1].[y]
  </code></pre>
  
  <p>The point above being the <code>SUM</code> performed in the database. However, with Linq-to -Entities in Entity Framework Core (2.0), I get something like the following:</p>
  
  <pre><code>SELECT [data0].[x], [data0].[y], [data0].[something], [...]
  FROM [table] AS [data0]
  ORDER BY [data0].[x], [data0].[y]
  </code></pre>
  
  <p>The point this time being no summarizing the data at database level (but rather in memory after all the data has been fetched). It should come as no surprise that this takes <em>a lot</em> longer.</p>
  
  <p>I've searched and found similar questions answered, but nothing that I get in terms of how it translates to my specific query. I have two questions:</p>
  
  <ol>
  <li>How can I produce the optimized result (summarizing at database level) with Linq-to-Entities?</li>
  <li>As a bonus, if you know to explain some sort of reasoning behind this all that makes the behavior predictable?</li>
  </ol>
  
  <p>(I know I can execute specific SQL using <code>DbCommand</code> or something, but that's not the answer I'm looking for - I want Linq ;)</p>
  
  <p><strong>[EDIT]</strong>
  It's been suggested that <a href="https://stackoverflow.com/questions/41569461/groupby-first-result-of-lookup-table-in-entity-framework-core">this question and its answer</a> makes my question a duplicate of the same. I read that Q'n'A, but wasn't satisfied, before posting my own question. Here are some (of my) reasons why that was:</p>
  
  <ul>
  <li>The claim in the answer that <code>GroupBy</code> is always processed in-memory seemed unsubstantiated (but more on that below).</li>
  <li>That other question is about a specific query generating (among other things) errors, and the answer contains a solution to that problem which is not applicable to my situation.</li>
  <li>That other question - or rather the answer - does not specifically address the performance issues (of my context), and does not contain much of a discussion on how Linq queries translate to SQL in EF Core.</li>
  <li><del>The answer makes claims without referencing versions etcetera, and may soon become obsolete.</del> </li>
  </ul>
  
  <p>So, what about the <code>GroupBy</code>-in-memory claim? Well, I agree that this piece of information is relevant to my case. Obviously summarizing on groups at the database level is not possible if grouping is performed in-memory. In the comments section of the above mentioned answer, there is indeed also a reference to the <a href="https://github.com/aspnet/EntityFrameworkCore/wiki/Roadmap" rel="nofollow noreferrer">EF Core Road-map</a> that I overlooked before. From what I gather, proper grouping will be included in the 2.1 release scheduled for Q4 2017.</p>
  
  <p>I now think that the answer to my question here, pending that release of EF Core 2.1, should be a clear reference to the documentation and road-map. It remains to be seen if the actual summarizing problem that I have will be solved along with the grouping issues. (I don't see why it shouldn't, but still...) </p>
  
  <p>Such an answer could be updated when new information becomes available.</p>