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 });

Put the same query in a stored procedure which is already returning the data in your desired format and call that stored procedure from EF. You can import the stored procedure as a function which can be called from your DAL layer — RBT, Jun 08 '16 at 10:03
I suppose you can use union all (Concat in LINQ), but it might be better to just append total row by hand (either in user interface, or just to the resulting dataset), because you have all data to do this in memory already. — Evk, Jun 08 '16 at 10:04

jdweng · Accepted Answer · 2016-06-08T10:22:08.290

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 });
        }
    }
}

You are using ADO.NET Datatables. The question is about Entity Framework — llouk, Jun 08 '16 at 10:22
I assume the results of the query would be put into c# DataTable. The posting is tagged with c# as well as Entity Framework. — jdweng, Jun 08 '16 at 11:24

Entity Framework 6: Add a summary row with totals

1 Answers1