Dynamic DataTable Group by and Aggregate functions

Question

I'm trying to apply SQL-Like group by on a datatable I have. However I can't simply use LINQ answers others have suggested, as I don't know columns I have before runtime - user selects them. However each column will either have an Aggregate function or be included in the group by. Imagine table like this one

WW     XXX     YYYY
1        A        B
2        A        B
3        C        C
4        C

I only know that user wants to group by one column - then I want to apply COUNT on the rest of the columns so the new table if user selected XXX looks like:

COUNT(WW)     XXX     COUNT(YYYY)
2               A               2
2               C               1

Can you show what you have tried with the C# code and DataTable? — Yom T., Dec 13 '18 at 09:31
I don't have much knowledge of LINQ, so I only tried to adapt the solutions from here: https://stackoverflow.com/questions/19407387/how-do-i-use-select-group-by-in-datatable-selectexpression however I couldn't get any of them to work — Ladislav Louka, Dec 13 '18 at 09:36

Tim Schmelter · Accepted Answer · 2018-12-13T09:47:47.623

You could use this approach:

string columnToGroup = "XXX";  // user selected
DataTable aggrTable = new DataTable();
aggrTable.Columns.Add(columnToGroup);

var aggrColumns = tbl.Columns.Cast<DataColumn>()
    .Where(c => !c.ColumnName.Equals(columnToGroup, StringComparison.InvariantCultureIgnoreCase));

foreach (DataColumn col in aggrColumns)
    aggrTable.Columns.Add(col.ColumnName, typeof(int));

var grpQuery = tbl.AsEnumerable().GroupBy(r => r[columnToGroup]);
foreach (var grp in grpQuery)
{
    DataRow row = aggrTable.Rows.Add();
    row[columnToGroup] = grp.Key;
    foreach (DataColumn col in aggrColumns)
        row[col.ColumnName] = grp.Count(r => !r.IsNull(col.ColumnName));
}

score 0 · Answer 2 · answered Dec 13 '18 at 10:30

I do agree that you cannot use Linq for dynamically choosing column case. Though you may need to change the column name later but the following should work.

DataTable table = new DataTable();
table.Columns.Add("Col1", typeof(int));
table.Columns.Add("Col2", typeof(string));
table.Columns.Add("Col3", typeof(string));


//Your data
table.Rows.Add(1, "A", "B");
table.Rows.Add(2, "A", "B");
table.Rows.Add(3, "C", "C");
table.Rows.Add(4, "C", "");

//Type
string chosen = "Col2"; //User-chosen grouping type.
List<string> columns = new List<string> { "Col1", "Col2", "Col3" };


//LINQ 
var query = table.AsEnumerable().GroupBy(x => x[chosen]).Select(x => {

    ArrayList sampleObject = new ArrayList();
    foreach (var count_col in columns)
    {
        if (count_col == chosen)
        {
            sampleObject.Add(x.Key.ToString());
        }
        else
        {
            sampleObject.Add(x.Sum(y => y[count_col].ToString() == "" ? 0 : 1));
        }

    }

    return sampleObject;
});

//result
foreach (var r in query)
{
    Console.WriteLine("{0}\t{1}\t{2}", r[0], r[1], r[2]);
}

Dynamic DataTable Group by and Aggregate functions

2 Answers2