title: "Case statement" in a "group by query" with Entity Framework 6
body: <p>I'm working with Entity Framework 6 and SQL Server 2014.</p>
  
  <p>I need to do a group by query. This is easy, but now I need a special case. Here an example to clarify what I need.</p>
  
  <p>Example:</p>
  
  <pre><code>Table
  
  ID  COLOR    NAME  
  1    red1    aaa
  2    red2    vvv
  3    green1  fff
  4    green2  ggg 
  5    yellow  eee
  </code></pre>
  
  <p>Let's suppose I have to group by COLOR, <strong>BUT</strong> with a bond: I want to group together the value <em>red1</em> and <em>red2</em> as <em>red</em> and <em>green1</em> and <em>green2</em> as <em>green</em>.</p>
  
  <p>So, if I make a <em>groupby&amp;count</em> query, the result'd be:</p>
  
  <pre><code>  Output 
  
    COLOR    COUNT
    red      2
    green    2
    yellow   1
  </code></pre>
  
  <p>Is it possible with Entity Framework? How?</p>
  
  <p>This is one of my group by stastement. Can I modify it to reach my goal or is it something different to do?</p>
  
  <pre><code>var groupedData = MyData.GroupBy(x =&gt; x.Products.NAME).Select(g =&gt; new { Product = g.Key, Total = g.Sum(x =&gt; x.IMPORT), Quantity = g.Sum(x =&gt; x.QTY) } ) ;
  </code></pre>
  
  <p>UPDATE -> SECOND SCENARIO</p>
  
  <p>Ok for the proposed solution.</p>
  
  <p>Is it possible to create this output?</p>
  
  <pre><code>  Output 
  
    COLOR    COUNT
    red      2
    green    2
    yellow   1
    allColor 5
  </code></pre>
  
  <p>So, as you can see, I'm asking you how to add a row in my query with the aggregate calculate on all rows. Is it possible? </p>