title: Entity Framework grouped query translated into multiple SELECTs
body: <p>Can someone help and tell me why this LINQ query is being translated in EF to multiple SELECTs ?</p>
  
  <pre><code>var query = db.ReportedFulfillments.GroupBy(x =&gt; x.ContractId).Select(grouping =&gt; new
      {
           ContractId = grouping.Key,
           Total = grouping.Count(),
           FU = grouping.Count(x =&gt; x.Month == 1 &amp;&amp; x.Value == 1),
           BR = grouping.Count(x =&gt; x.Month == 1 &amp;&amp; x.Value == 2)
      }
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/VEF8q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VEF8q.png" alt="enter image description here"></a></p>
  
  <p>I thought that EF will output something like this:</p>
  
  <pre><code>SELECT ContractId,
   Count(*) AS Total,
   COUNT(CASE WHEN [Month] = 1 AND [Value] = 1 THEN Value END) AS FU,
   COUNT(CASE WHEN [Month] = 1 AND [Value] = 2 THEN Value END) AS BR,
  FROM ReportedFulfillments GROUP BY ContractId
  </code></pre>
  
  <p>I'm using EntityFramework 6.2.0</p>