Entity Framework 6: Add a summary row with totals

Question

How can I do this with Entity Framework?

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.

Table

ID  COLOR    NAME  
1    red    aaa
2    red    vvv
3    green  fff
4    green  ggg 
5    yellow  eee

Let's suppose I have to group by COLOR, BUT I'd like also to create one further record with the aggregate on ALL rows.

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

  Output 

  COLOR    COUNT
  red      2
  green    2
  yellow   1
  allColor 5

In sql it is possible, as you can see in the above posted link. How can I reach my goal with Entity Framework?

In my real case I tried this, but the last line is red-underlayed in visual studio.

var tempErogs = erogs.GroupBy(x => 0).Select(g => new { Modalita = g.Key, Importo = g.Sum(x => x.IMPORTO), Litri = g.Sum(x => x.LITRI), N_erogs = g.Count() }).ToList();
erogsGBvenduto.Add(new { Modalita = tempErogs[0].Modalita, Importo = tempErogs[0].Importo, Litri = tempErogs[0].Litri, N_erogs = tempErogs[0].N_erogs });

Answer 1

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication99
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("ID", typeof(int));
            dt1.Columns.Add("COLOR", typeof(string));
            dt1.Columns.Add("NAME", typeof(string));

            dt1.Rows.Add(new object[] { 1, "red", "aaa"});
            dt1.Rows.Add(new object[] { 2, "red", "vvv"});
            dt1.Rows.Add(new object[] { 3, "green", "fff"});
            dt1.Rows.Add(new object[] { 4, "green", "ggg"});
            dt1.Rows.Add(new object[] { 5, "yellow", "eee"});

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("COLOR", typeof(string));
            dt2.Columns.Add("COUNT", typeof(int));

            var groups = dt1.AsEnumerable().GroupBy(x => x.Field<string>("COLOR"));
            foreach(var group in groups)
            {
                dt2.Rows.Add(new object[] {group.Key, group.Count()});
            }
            dt2.Rows.Add(new object[] { "allColor", dt1.Rows.Count });
        }
    }
}