c# - How to group datatable by unknown column names and calculate sum of one field?

Question

I have a table like this:

 Name    Age  Gender  
 Sasha   12      W  
 Sasha   20      W  
 Sasha   21      M  
 Bob     21      M

And I want to group by multiple fields, for example [Name] and [Gender] and sum this by field [Age]. The columns are unknown at compile time because the user can select them.

So, in in this example i want this:

 Name    Age  Gender  
 Sasha   32      W  
 Sasha   21      M  
 Bob     21      M

But I can't do it by LINQ, because I don't know the columns at compile time.

Thanks for answers!

Answer 1

You can use an anonymous type if you want to group by multiple columns.

var ageSumsPerNameAndGender = table.AsEnumerable()
    .GroupBy(row => new { Name = row.Field<string>("Name"), Gender = row.Field<string>("Gender") })
    .Select(group => new
    {
        Name = group.Key.Name,
        Gender = group.Key.Gender,
        SumOfAge = group.Sum(row => row.Field<int>("Age"))
    });

If you want to ouput this you could use a foreach-loop:

Console.WriteLine("Name Age Gender");
foreach(var x in ageSumPerNamegenders)
    Console.WriteLine("{0} {1} {2}", x.Name, x.SumOfAge, x.Gender);

---

According to your comments it seems that you actually don't know the columns because the user specifies them. Then it's more difficult and error-prone.

One way is to provide a custom IEqualityComparer<T> for multiple fields. This should work:

public class MultiFieldComparer : IEqualityComparer<IEnumerable<object>>
{
    public bool Equals(IEnumerable<object> x, IEnumerable<object> y)
    {
        if(x == null || y == null) return false;
        return x.SequenceEqual(y);
    }

    public int GetHashCode(IEnumerable<object> objects)
    {
        if(objects == null) return 0;
        unchecked  
        {
            int hash = 17;
            foreach(object obj in objects)
                hash = hash * 23 + (obj == null ? 0 : obj.GetHashCode());
            return hash;
        }
    }
}

Now you can use an instance of this comparer for Enumerable.GroupBy (and many other LINQ methods). Here is a working example:

List<string> columnNames = new List<string> { "Name", "Gender" };

var columnsToGroupBy = table.Columns.Cast<DataColumn>()
    .Where(c => columnNames.Contains(c.ColumnName, StringComparer.InvariantCultureIgnoreCase))
    .ToArray();
var comparer = new MultiFieldComparer();
var summed = table.AsEnumerable()
    .GroupBy(row => columnsToGroupBy.Select(c => row[c]), comparer)
    .Select(group => new
    {
        AllFields = group.Key,
        Sum = group.Sum(row => row.IsNull("Age") ? 0 : decimal.Parse(row["Age"].ToString()))
    });
foreach (var x in summed)
{
    Console.WriteLine("{0} Sum: {1}", string.Join(" ", x.AllFields), x.Sum);
}

As you can see i've hardcoded "Age" as sum-column. It must be a numeric column, so you have to ensure that. You could also let the user provide it. But again, it must be parsable to decimal otherwise it doesn't work.