如何使用LINQ对某些列进行分组和排序 [英] How to use LINQ to group by and order by certain column
问题描述
已更新:在作为日期字段的DateTimeCombined列中添加了将日期和时间列作为字符串字段的新列
UPDATED: added a new column combining the Date and Time column which are string fields into DateTimeCombined column which is a DateTime field
因此LINQ要做的是按名称分组，并为每个具有最早日期和时间的名称获取行.然后，应在该行的其余部分添加名称.
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初始化:
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));
这是开始的DataTable(默认情况下会检索到):
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
第一个LINQ函数应该是为每个名称获取更早的DateTimeCombined:
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
下一个功能应该是按DateTimeCombined进行排序:
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
下一个功能应该是为每个名称放置其余行(最终的DataTable应该看起来像这样):
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
注意:例如，如果凯利是约翰(约翰两次出现)，那么Ardsley小组将排在Orlance之前.
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();
更新:
var ordered = dataTable.AsEnumerable()
.OrderBy(en => en.Field<DateTime>("DateTimeCombined"))
.GroupBy(en1 => en1.Field<string>("Name")).ToList();
只给我名字.
更新:
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();
以上内容按预期工作:
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
但仅当我参加每组的第一行时(按名称).如果我按组划分所有行，则所有行混合在一起.我现在该如何在Mike的行后追加Mike's行的其余部分，在Jenny的行后追加Jenny's的其余行，依此类推?可以在同一个LINQ中完成吗?
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?
推荐答案
基于新编辑的问题，我有以下内容:
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();
这篇关于如何使用LINQ对某些列进行分组和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
