C# LINQ - How to build Group By clause dynamically -

C# LINQ - How to build Group By clause dynamically -

i working on application user can select columns he/she wants see on screen , columns grouping or aggregate. so, in linq section should pass variables hold column names both grouping , aggregate clause. maintain in mind datatable dt may hold different info every time(e.g. employee info, purchase orders, performance stats, etc). can info info @ run time via dt.columns[i].columnname , dt.columns[i].datatype.name. can 1 advise how that, need this:

sqldataadapter da = new sqldataadapter(cmd); datatable dt = new datatable(); da.fill(dt); var query = row in dt.asenumerable() grouping row new { foreach(datacolumn column in dt.columns) { row[column.columnname]; } } grp select new { foreach(datacolumn column in dt.columns) { if(column.datatype.name == "decimal") { sum(grp[column.columnname]); }else{ grp[column.columnname]; } } };

there several ways this. here's one.

below class utilize pretty called ntuple. same thought tuple<t>, tuple<t1, t2>, etc classes come .net framework. however, ntuple class designed hold variable number of items. 2 ntuple instances equal if contain same number of values , values equal.

given set of columns

// per op, list of columns grouping generated @ runtime ienumerable<string> columnstogroupby = ...;

you can utilize ntuple class grouping columns this:

var groups = dt.asenumerable() .groupby(r => new ntuple<object>(from column in columnstogroupby select r[column]));

here's beef:

public class ntuple<t> : iequatable<ntuple<t>> { public ntuple(ienumerable<t> values) { values = values.toarray(); } public readonly t[] values; public override bool equals(object obj) { if (referenceequals(this, obj)) homecoming true; if (obj == null) homecoming false; homecoming equals(obj ntuple<t>); } public bool equals(ntuple<t> other) { if (referenceequals(this, other)) homecoming true; if (other == null) homecoming false; var length = values.length; if (length != other.values.length) homecoming false; (var = 0; < length; ++i) if (!equals(values[i], other.values[i])) homecoming false; homecoming true; } public override int gethashcode() { var hc = 17; foreach (var value in values) hc = hc*37 + (!referenceequals(value, null) ? value.gethashcode() : 0); homecoming hc; } }

here's test case:

static void main(string[] args) { // sample info var dt = new datatable(); dt.columns.add("name", typeof(string)); dt.columns.add("city", typeof(string)); dt.columns.add("state", typeof(string)); dt.columns.add("value", typeof(double)); dt.rows.add("mike", "tallahassee", "fl", 3); dt.rows.add("mike", "tallahassee", "fl", 6); dt.rows.add("steve", "tallahassee", "fl", 5); dt.rows.add("steve", "tallahassee", "fl", 10); dt.rows.add("steve", "orlando", "fl", 7); dt.rows.add("steve", "orlando", "fl", 14); dt.rows.add("mike", "orlando", "ny", 11); dt.rows.add("mike", "orlando", "ny", 22); // "configuration" info ienumerable<string> columnstogroupby = new[] {"city", "state"}; string columntoaggregate = "value"; // test routine foreach (var grouping in dt.asenumerable().groupby(r => new ntuple<object>(from column in columnstogroupby select r[column]))) { foreach (var keyvalue in group.key.values) { debug.write(keyvalue); debug.write(':'); } debug.writeline(group.sum(r => convert.todouble(r[columntoaggregate]))); } }

c# linq group-by aggregate dynamic-linq