CODEWITHSUNDEEP
c#sortingdatatable
Contissi
Contissi

Reputation: 41

C# DataTable - GroupBy then OrderBy

I have a DataTable that contains 5 columns.

Activity (string) Clock In (DateTime) Clock Out (DateTime) Total (double) Type (int)

I am needing to GroupBy the Clock Out DateTime.Date and within that OrderBy the Type.

So essentially, if I have data, I want it grouped/order like this:

Activity    Clock In           Clock Out          Total    Type

Drawing     09/16/13 13:30     09/16/13 13:32     0.02     1
Drawing     09/16/13 13:40     09/16/13 13:42     0.02     1
Testing     09/16/13 13:50     09/16/13 13:52     0.02     1
Testing     09/16/13 13:30     09/16/13 13:34     0.04     2
Testing     09/16/13 13:40     09/16/13 13:54     0.14     2


Drawing     09/17/13 13:50     09/17/13 13:52     0.02     1
Testing     09/17/13 13:30     09/17/13 13:34     0.04     2
Testing     09/17/13 13:40     09/17/13 13:54     0.14     2


Testing     09/18/13 13:52     09/18/13 13:54     0.02     2

I have all the data in a DataTable already, I just need help with the grouping...

Any thoughts?? I have tried:

groupedSortedTbl = dt.AsEnumerable()
                    .GroupBy(d => d.Field<DateTime>("CLOCK_OUT").Date)
                    .SelectMany(g => g.OrderBy(t => t.Field<int>("Type")))
                    .CopyToDataTable();

Upvotes: 0

Views: 3080

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460228

Do you want to group by Clock Out? If so, why do you use SelectMany to flatten them out afterwards at all? You could use OrderBy + ThenBy instead.

DataTable groupedSortedTbl = dt.AsEnumerable()
    .OrderBy(row => row.Field<DateTime>("CLOCK_OUT").Date)
    .ThenBy(row  => row.Field<int>("Type"))
    .CopyToDataTable();

If you want to keep the groups, you cannot use CopyToDataTable at the end to create the DataTable. So you could create an IEnumerable<Anonymous/Custom Type> instead. Or you could select many DataTables, one for every date-group:

IEnumerable<DataTable> dateGroups = dt.AsEnumerable()
    .GroupBy(row => row.Field<DateTime>("CLOCK_OUT").Date)
    .Select(g => g.OrderBy(t => t.Field<int>("Type")).CopyToDataTable());

Upvotes: 0

Kevin
Kevin

Reputation: 4636

I think you want to do this... 

groupedSortedTbl = dt.AsEnumerable()
                    .OrderBy(d => d.Field<DateTime>("CLOCK_OUT").Date)
                    .ThenBy(t => t.Field<int>("Type"))
                    .CopyToDataTable();

Upvotes: 2

Related Questions