0

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!

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
gado
  • 33
  • 1
  • 7
  • Could you please provide us with more information? How you get your datatable for instance? – Christos Mar 12 '15 at 12:03
  • Look at http://stackoverflow.com/questions/5601752/how-to-sum-columns-in-a-datatable & http://stackoverflow.com/questions/5892993/how-to-calculate-the-sum-of-the-datatable-column-in-asp-net – Amirhossein Mehrvarzi Mar 12 '15 at 12:05
  • Thanks for reply! I get this datatable in runtime by join of two tables – gado Mar 12 '15 at 12:06

1 Answers1

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.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I can't, because I can't create anonymous type in runtime - I don't know how many columns I need to group – gado Mar 12 '15 at 12:10
  • @gado: so what is your actual requirement then? You want "this"(Name Age Gender) but you don't know if you have it? – Tim Schmelter Mar 12 '15 at 12:13
  • it's example, my real table can hold different colums. Columns for grouping I get from user – gado Mar 12 '15 at 12:16
  • @gado: that's problematic since you want to sum fields but you don't even know if those fields exist or if you can sum them at all. What would you expect it to do if the column you sum is a string? So the question is: why is the user allowed to provide columns that are out of your control? Provide him a list of available columns. – Tim Schmelter Mar 12 '15 at 12:17
  • I my case I always can sum this field, and all needed fields are exist – gado Mar 12 '15 at 12:20
  • User select all fields from list – gado Mar 12 '15 at 12:25