C# LINQ - How to build Group By clause dynamically

aggregate c# dynamic-linq group-by linq

Question

I'm working on an application that will let users choose which columns to aggregate or group by as well as the columns they wish to appear on the screen. Therefore, I should really provide variables that include column names to the group by and aggregate clauses in my LINQ section. Do not forget thatDataTable dt may include varying data each time (e.g. Employee info, Purchase orders, Performance stats, etc). I can only get data at runtime via throughdt.Columns[i].ColumnName and dt.Columns[i].DataType.Name . Can somebody provide me advice on how to implement that? I need something that looks 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];
                           }

                        }
                    };
1
5
11/3/2014 8:24:16 PM

Accepted Answer

There are several methods for doing this. This is one.

The NTuple class, which I use rather often, is seen below. It has the same concept as the.NET framework's TupleT>, TupleT1, T2>, etc. classes. The NTuple class, on the other hand, may store a variety of things. If two NTuple instances have the same number of values and the same values, then they are equivalent.

an assortment of columns

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

To group by those columns, use the NTuple class as follows:

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

The problem is this:

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 is a test scenario:

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])));
    }
}
11
11/3/2014 8:05:22 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow