C# DataTable How to sort the solution after grouping?

Question

I have a table. I grouped that table's rows. The columns are optional. Columns of the table: day, time, country, city, rain (millimeter). I would like to copy the solution to a new table.

Code:

var q = baseTable.AsEnumerable()
.GroupBy(row => columnsToGroupBy.Select(c => row[c]), comparer) //eg: day, time, country
.Select(group => new {
AllKeys = group.Key,
AllField = group.Sum(row => double.Parse(row["rain"].ToString()))});

Eg. after the grouping it shows how much rain has fallen.

q[0]: { AllKeys { "Tuesday, "06:00", "Austria" }, AllField { "1" } }
q[1]: { AllKeys { "Monday", "12:00", "Slovakia" }, AllField { "3" } }
q[2]: { AllKeys { "Tuesday, "06:00", "Slovakia" }, AllField { "1,5" } }
q[3]: { AllKeys { "Monday", "11:00", "Slovakia" }, AllField { "4" } }

I would like to sort by the keys in ascending order:

q[0] tartalma { AllKeys { "Monday", "11:00", "Slovakia" }, AllField { "4" } }
q[1] tartalma { AllKeys { "Monday", "12:00", "Slovakia" }, AllField { "3" } }
q[2] tartalma { AllKeys { "Tuesday, "06:00", "Austria" }, AllField { "1,5" } }
q[3] tartalma { AllKeys { "Tuesday, "06:00", "Slovakia" }, AllField { "1" } }

IF the user would like to change the sorting by the day and time, than it have to sort the columns to day and time. So this is not work, because I don't know how many keys will be:

var q2 = q.OrderBy(x => x.AllKeys.First()))
.ThenBy(x => x.AllKeys.ToList()[1]);

Can you help me, how to sort the query?

You can sort them after you group them when selecting like this `{ AllKeys = group.Key.OrderBy(t=>t),...` — Eldar, Nov 27 '19 at 18:47
Is the `GroupBy` working for you? Are you using a special `comparer` that compares `IEnumerable — NetMage, Nov 27 '19 at 19:00
You will need an extension method to copy the result to a new `DataTable`. What type do you expect to have in the `AllKeys` column? — NetMage, Nov 27 '19 at 19:02
Does this answer your question? [Dynamic LINQ OrderBy on IEnumerable / IQueryable](https://stackoverflow.com/questions/41244/dynamic-linq-orderby-on-ienumerablet-iqueryablet) — Eugene Podskal, Nov 27 '19 at 19:30
{ AllKeys = group.Key.OrderBy(t=>t),... it's only sort the keys in the query row. — Lother, Nov 27 '19 at 20:36
Yes the comparer is working, I got the idea from here: https://stackoverflow.com/questions/29009231/how-to-group-datatable-by-unknown-column-names-and-calculate-sum-of-one-field — Lother, Nov 27 '19 at 20:36
Currently the type of the AllKeys is: System.Linq.Enumerable.WhereSelectArrayIterator — Lother, Nov 27 '19 at 20:37
@EugenePodskal I tried with this: var ordered = summed.OrderBy("AllKeys").ToArray(); but it's throw error: — Lother, Nov 27 '19 at 23:11
@EugenePodskal Error: Exception thrown: 'Microsoft.CSharp.RuntimeBinder.RuntimeBinderException' in System.Core.dll — Lother, Nov 27 '19 at 23:11

NetMage · Accepted Answer · 2019-12-02T20:18:10.327

Just like you can provide a custom IEqualityComparer for GroupBy, you can provide a custom IComparer for OrderBy. Basically, AllKeys is of type IEnumerable<object>, so you need an IComparer that takes those and compares each element with the default comparer, but you have to get that using Reflection:

public class EnumerableComparer : IComparer<IEnumerable<object>> {
    public int Compare(IEnumerable<object> xs, IEnumerable<object> ys) {
        var xe = xs.GetEnumerator();
        var ye = ys.GetEnumerator();

        var sofar = 0;
        while (sofar == 0 && xe.MoveNext()) {
            if (!ye.MoveNext())
                sofar = 1;
            else {
                var ct = typeof(Comparer<>).MakeGenericType(xe.Current.GetType());
                var c = ct.GetProperty("Default").GetValue(null);
                var fc = ct.GetMethod("Compare");
                sofar = (int)fc.Invoke(c, new[] { xe.Current, ye.Current });
            }
        }
        if (sofar == 0 && ye.MoveNext())
            sofar = -1;

        return sofar;
    }
}

With this available, you can do:

var q = baseTable.AsEnumerable()
                 .GroupBy(row => columnsToGroupBy.Select(c => row[c]), comparer) //eg: day, time, country
                 .Select(group => new {
                     AllKeys = group.Key,
                     AllField = group.Sum(row => double.Parse(row["rain"].ToString()))
                 })
                 .OrderBy(r => r.AllKeys, new EnumerableComparer());

If you want to convert the result to a DataTable you'll need to use Reflection but you have the problem of deciding what to do with your AllKeys in the result column(s).

Thank you very much! I create the table from the list of the columns and the operations what the user choose, not from the query. A foreach on this query will fill the DataTable. — Lother, Nov 27 '19 at 23:48
I'd be happy if you could send me sources for learning these techniques. — Lother, Nov 27 '19 at 23:59
@Lother I no longer know all my sources, but I would say a lot of Stack Overflow searching can help you put the pieces together, and a lot of experimentation in LINQPad can help with understanding (the `Dump` method especially) of object structures. — NetMage, Nov 28 '19 at 00:54
The only issue with this answer is that it will be done in-memory instead of DB, but depending on the number of keys it may even be for better. — Eugene Podskal, Nov 28 '19 at 13:11
@EugenePodskal Is there some way to work with `DataTable` that isn't in memory? Note that the original question has `AsEnumerable()` immediately after the `baseTable` and is using field selection syntax for `DataTable`. — NetMage, Dec 02 '19 at 19:58

C# DataTable How to sort the solution after grouping?

1 Answers1

Linked