c# - How to use LINQ to group by and order by certain column

Question

UPDATED: added a new column combining the Date and Time column which are string fields into DateTimeCombined column which is a DateTime field

So what the LINQ should do is group by name column and get the row for each name that has the earliest date+time. Then it should add the rest of the row for the name.

DataTable init:

dataT = new DataTable();
dataT.Columns.Add("Date", typeof(string));
dataT.Columns.Add("Time", typeof(string));
dataT.Columns.Add("Day", typeof(string));
dataT.Columns.Add("Name", typeof(string));
dataT.Columns.Add("Place", typeof(string));
dataT.Columns.Add("DateTimeCombined", typeof(DateTime));
dataT.Columns.Add("NameMessage", typeof(string));

So here is the starting DataTable (which is retrieved by default):

Date        Time        Day     Name        Place       DateTimeCombined            NameMessage
6/29/2017   8:30AM      MON     John        Orlance     6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     John        Orlance     6/29/2017 8:40:00 AM
6/29/2017   8:50AM      MON     John        Orlance     6/29/2017 8:50:00 AM        
6/29/2017   9:10AM      MON     John        Orlance     6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     John        Orlance     6/29/2017 9:20:00 AM
6/29/2017   1:00PM      MON     John        Orlance     6/29/2017 1:00:00 PM
6/30/2017   8:30AM      TUE     John        Orlance     6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     John        Orlance     6/30/2017 8:40:00 AM
6/29/2017   8:15AM      MON     Mike        Atlanta     6/29/2017 8:15:00 AM
6/29/2017   8:30AM      MON     Mike        Atlanta     6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     Mike        Atlanta     6/29/2017 8:40:00 AM
6/29/2017   9:10AM      MON     Mike        Atlanta     6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     Mike        Atlanta     6/29/2017 9:20:00 AM
6/30/2017   8:30AM      TUE     Mike        Atlanta     6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     Mike        Atlanta     6/30/2017 8:40:00 AM
                                Christine   Marion                                  None
                                Steph       Kearney                                 None
6/29/2017   8:30AM      MON     Jenny       Boise       6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     Jenny       Boise       6/29/2017 8:40:00 AM
6/29/2017   8:50AM      MON     Jenny       Boise       6/29/2017 8:50:00 AM
6/29/2017   9:10AM      MON     Jenny       Boise       6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     Jenny       Boise       6/29/2017 9:20:00 AM
6/30/2017   8:30AM      TUE     Jenny       Boise       6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     Jenny       Boise       6/30/2017 8:40:00 AM
6/29/2017   8:30AM      MON     Kelly       Ardsley     6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     Kelly       Ardsley     6/29/2017 8:40:00 AM
6/29/2017   8:50AM      MON     Kelly       Ardsley     6/29/2017 8:50:00 AM
6/29/2017   9:10AM      MON     Kelly       Ardsley     6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     Kelly       Ardsley     6/29/2017 9:20:00 AM
6/30/2017   8:30AM      TUE     Kelly       Ardsley     6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     Kelly       Ardsley     6/30/2017 8:40:00 AM
                                Joseph      Houston                                 None

The first LINQ function should be to get the earlier DateTimeCombined for each Name:

6/29/2017   8:30AM      MON     John        Orlance     6/29/2017 8:30:00 AM
6/29/2017   8:15AM      MON     Mike        Atlanta     6/29/2017 8:15:00 AM
6/29/2017   8:30AM      MON     Jenny       Boise       6/29/2017 8:30:00 AM
6/29/2017   8:30AM      MON     Kelly       Ardsley     6/29/2017 8:30:00 AM

The next function should be to order that by DateTimeCombined:

- If `DateTimeCombined` is same, order first by `DateTimeCombined` and then by Name.

- If `DateTimeCombined` is same AND Name is same, order first by `DateTimeCombined` and then by Name and then by Place.


6/29/2017   8:15AM      MON     Mike        Atlanta     6/29/2017 8:15:00 AM
6/29/2017   8:30AM      MON     Jenny       Boise       6/29/2017 8:30:00 AM
6/29/2017   8:30AM      MON     John        Orlance     6/29/2017 8:30:00 AM
6/29/2017   8:30AM      MON     Kelly       Ardsley     6/29/2017 8:30:00 AM

The next function should be to put the rest of the rows for each name (the final DataTable should look like this):

6/29/2017   8:15AM      MON     Mike        Atlanta     6/29/2017 8:15:00 AM
6/29/2017   8:30AM      MON     Mike        Atlanta     6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     Mike        Atlanta     6/29/2017 8:40:00 AM
6/29/2017   9:10AM      MON     Mike        Atlanta     6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     Mike        Atlanta     6/29/2017 9:20:00 AM
6/30/2017   8:30AM      TUE     Mike        Atlanta     6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     Mike        Atlanta     6/30/2017 8:40:00 AM
6/29/2017   8:30AM      MON     Jenny       Boise       6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     Jenny       Boise       6/29/2017 8:40:00 AM
6/29/2017   8:50AM      MON     Jenny       Boise       6/29/2017 8:50:00 AM
6/29/2017   9:10AM      MON     Jenny       Boise       6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     Jenny       Boise       6/29/2017 9:20:00 AM
6/30/2017   8:30AM      TUE     Jenny       Boise       6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     Jenny       Boise       6/30/2017 8:40:00 AM
6/29/2017   8:30AM      MON     John        Orlance     6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     John        Orlance     6/29/2017 8:40:00 AM
6/29/2017   8:50AM      MON     John        Orlance     6/29/2017 8:50:00 AM        
6/29/2017   9:10AM      MON     John        Orlance     6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     John        Orlance     6/29/2017 9:20:00 AM
6/29/2017   1:00PM      MON     John        Orlance     6/29/2017 1:00:00 PM
6/30/2017   8:30AM      TUE     John        Orlance     6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     John        Orlance     6/30/2017 8:40:00 AM
6/29/2017   8:30AM      MON     Kelly       Ardsley     6/29/2017 8:30:00 AM
6/29/2017   8:40AM      MON     Kelly       Ardsley     6/29/2017 8:40:00 AM
6/29/2017   8:50AM      MON     Kelly       Ardsley     6/29/2017 8:50:00 AM
6/29/2017   9:10AM      MON     Kelly       Ardsley     6/29/2017 9:10:00 AM
6/29/2017   9:20AM      MON     Kelly       Ardsley     6/29/2017 9:20:00 AM
6/30/2017   8:30AM      TUE     Kelly       Ardsley     6/30/2017 8:30:00 AM
6/30/2017   8:40AM      TUE     Kelly       Ardsley     6/30/2017 8:40:00 AM

Note: If Kelly was John (John appearing twice), for example, then the Ardsley group would go before Orlance.

What I tried so far:

var ordered = dataTable.AsEnumerable().OrderBy(en => en.Field<DateTime>("DateTimeCombined")).CopyToDataTable();

Update:

var ordered = dataTable.AsEnumerable()
                .OrderBy(en => en.Field<DateTime>("DateTimeCombined"))
            .GroupBy(en1 => en1.Field<string>("Name")).ToList();

Just gives me the name only.

Update:

  var q = dataTable.AsEnumerable()
                .GroupBy(item => item.Field<string>("Name"))
                .SelectMany(grouping => grouping.Take(1))
                .OrderBy(item => item.Field<DateTime>("CombinedDateTime"))
                .ThenBy(item => item.Field<string>("Name"))
                .ThenBy(item => item.Field<string>("Place"))
                .CopyToDataTable();

The above works as expected:

6/29/2017   8:15AM      MON     Mike        Atlanta     6/29/2017 8:15:00 AM
6/29/2017   8:30AM      MON     Jenny       Boise       6/29/2017 8:30:00 AM
6/29/2017   8:30AM      MON     John        Orlance     6/29/2017 8:30:00 AM
6/29/2017   8:30AM      MON     Kelly       Ardsley     6/29/2017 8:30:00 AM

but only if i take the first row of each group (by Name). If I take all by group, the rows get all mixed up. How can I now, append the rest of Mike's rows after the row for Mike, append the rest of Jenny's rows after the row for Jenny, and so forth? Can this be done in the same LINQ?

Answer 1

Based on the new edited question, I have this:

var ordered = dataT.AsEnumerable()
                   .GroupBy(en => new { Name = en.Field<string>("Name"), Place = en.Field<string>("Place") })
                   .OrderBy(eng => eng.Min(en => en.Field<DateTime>("DateTimeCombined")))
                   .ThenBy(eng => eng.Key.Name).ThenBy(eng => eng.Key.Place)
                   .SelectMany(eng => eng.OrderBy(en => en.Field<DateTime>("DateTimeCombined")), (eng, en) => en)
                   .CopyToDataTable();