How do I query a DataTable using Linq and group by a collection of columns?

Question

Given a DataTable called table with three columns: "ID" (int), "Parent" (int), and "Description" (string),

and given a List<string> called groupByColumns with values { "ID", "Parent" }

How do I build a Linq query that groups by all of the columns in the List?

The crux of my difficulty is that GroupBy requires an anonymous type and I don't know how to build that type from the List.

I know how to query a DataTable using Linq and how to group by multiple columns, but that requires that I know which columns I'm grouping by. In this case, I simply have a reference to some column names in a list. The statement below gives me the result I want, but it's hard-coded.

var _attempt = table.AsEnumerable()
               .GroupBy(i => (i[groupByColumns[0]], i[groupByColumns[1]]))
               .Select(grp => new { GroupID = grp.Key, DataList = grp.ToList() })
               .ToList();

I don't know how many values will be in the List. There may not be any-- and I can code for that special case as needed, but there may be as many strings in the List as there are columns in the DataTable, neither of which number I will know until runtime.

var table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Parent", typeof(int));
table.Columns.Add("Description", typeof(string));
// maybe more

table.Rows.Add(new object[] { 5, 1, "Whatever" });
// repeated

var groupByColumns = new List<string> ();
groupByColumns.Add("ID");
groupByColumns.Add("Parent");
// maybe more

var _attempt = table.AsEnumerable()
               .GroupBy(i => (i[groupByColumns[0]], i[groupByColumns[1]]))
               .Select(grp => new { GroupID = grp.Key, DataList = grp.ToList() })
               .ToList();

Answer 1

I'm not sure what is the best way to do this, but one of the easiest is to group by a string made up of all the required column values.

This does mean that the group key is a string and not the column values, but you can get around that by generating the Column Values for the first result of each group.

For example

Func<DataRow, List<object>> GetColumnValues = r => groupByColumns.Select(key => r[key]).ToList();

var _attempt = table.AsEnumerable()
             .GroupBy(r => String.Join(":", GetColumnValues(r)))
             .Select(grp => new { GroupID = GetColumnValues(grp.First() ), DataList = grp.ToList() })
             .ToList();

One disadvantage of this is that it can perform lots of string concatenation and so may not be suitable if the number of rows is extremely large.