C# LINQ - How to build Group By clause dynamically

Question

I am working on the application where user can select columns he/she wants to see on the screen and which columns to group by or aggregate. So, in my LINQ section I should actually pass variables that hold column names to both group by and aggregate clause. Keep in mind that DataTable dt may hold different data every time(e.g. Employee info, Purchase orders, Performance stats, etc). I can only get information about the data at run time via dt.Columns[i].ColumnName and dt.Columns[i].DataType.Name. Can any one advise how to do that, what I need is something like this:

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        var query = from row in dt.AsEnumerable()
                    group row by new
                    {
                        foreach(DataColumn column in dt.Columns)
                        {
                           row[column.ColumnName];                          
                        }
                    } into grp

                    select new
                    {
                        foreach(DataColumn column in dt.Columns)
                        {
                           if(column.DataType.Name == "Decimal")
                           {
                             Sum(grp[column.ColumnName]);
                           }else{
                             grp[column.ColumnName];
                           }

                        }
                    };

Answer 1

There are several ways to do this. Here's one.

Below is a class I use pretty often called NTuple. It is the same idea as the Tuple<T>, Tuple<T1, T2>, etc classes that come with the .NET framework. However, the NTuple class is designed to hold a variable number of items. Two NTuple instances are equal if they contain the same number of values and those values are equal.

Given a set of columns

// as per OP, the list of columns to group by will be generated at runtime
IEnumerable<string> columnsToGroupBy = ...; 

you can use the NTuple class to group by those columns like this:

var groups = dt.AsEnumerable()
    .GroupBy(r => new NTuple<object>(from column in columnsToGroupBy select r[column]));

Here's the 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))
            return true;
        if (obj == null)
            return false;
        return Equals(obj as NTuple<T>);
    }

    public bool Equals(NTuple<T> other)
    {
        if (ReferenceEquals(this, other))
            return true;
        if (other == null)
            return false;
        var length = Values.Length;
        if (length != other.Values.Length)
            return false;
        for (var i = 0; i < length; ++i)
            if (!Equals(Values[i], other.Values[i]))
                return false;
        return true;
    }

    public override int GetHashCode()
    {
        var hc = 17;
        foreach (var value in Values)
            hc = hc*37 + (!ReferenceEquals(value, null) ? value.GetHashCode() : 0);
        return hc;
    }
}

Here's a test case:

static void Main(string[] args)
{
    // some sample data
    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);

    // some "configuration" data
    IEnumerable<string> columnsToGroupBy = new[] {"CITY", "STATE"};
    string columnToAggregate = "VALUE";

    // the test routine
    foreach (var group 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])));
    }
}

Answer 2

A solution is to use the QueryByCube function provided by my component AdaptiveLINQ. Just build an Select expression using reflection . This expression is automatically converted by AdaptiveLINQ into a request using the GroupBy operator.