"Case statement" in a "group by query" with Entity Framework 6

Question

I'm working with Entity Framework 6 and SQL Server 2014.

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.

Example:

Table

ID  COLOR    NAME  
1    red1    aaa
2    red2    vvv
3    green1  fff
4    green2  ggg 
5    yellow  eee

Let's suppose I have to group by COLOR, BUT with a bond: I want to group together the value red1 and red2 as red and green1 and green2 as green.

So, if I make a groupby&count query, the result'd be:

  Output 

  COLOR    COUNT
  red      2
  green    2
  yellow   1

Is it possible with Entity Framework? How?

This is one of my group by stastement. Can I modify it to reach my goal or is it something different to do?

var groupedData = MyData.GroupBy(x => x.Products.NAME).Select(g => new { Product = g.Key, Total = g.Sum(x => x.IMPORT), Quantity = g.Sum(x => x.QTY) } ) ;

UPDATE -> SECOND SCENARIO

Ok for the proposed solution.

Is it possible to create this output?

  Output 

  COLOR    COUNT
  red      2
  green    2
  yellow   1
  allColor 5

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?

Answer 1

To fix it in the LINQ statement change:

GroupBy(x => x.Products.NAME).

to

GroupBy(x => { if (x.Products.NAME.StartsWith("green")) return "green"; 
               if (x.Products.NAME.StartsWith("red")) return "red"; 
               return x.Products.NAME; }).

Answering your update question. You should concatenate one line to your collection with totals. just use CONCAT with the new array with one element.

var groupedData = MyData.GroupBy(x => { 
          if(x.Products.NAME.StartsWith("green")) return "green";
          if (x.Products.NAME.StartsWith("red")) return "red"; 
          return x.Products.NAME; })
          .Select(g => new { Product = g.Key, Cnt = g.Count()} )
          .Concat(new[] { new { Product = "allColor", 
                                Cnt = MyData.Count() 
                                }
                          });