问题描述
我一直在寻找一种获取多列的方法,但是在SQL中仅按一个列进行分组,我发现了一些信息.但是我无法在linq中想出一种方法.
I have been looking for a way to get multiple columns but group by only one in SQL and I found some info. However I can not came up with a way to do it in linq.
我有以下玩具示例表:
| Id | Message | GroupId | Date |
|-------------------------------|
| 1 | Hello | 1 | 1:00 |
| 2 | Hello | 1 | 1:01 |
| 3 | Hey | 2 | 2:00 |
| 4 | Dude | 3 | 3:00 |
| 5 | Dude | 3 | 3:01 |
我想恢复具有不同 GroupId
的行的所有列,如下所示(以'Date'desc顺序):
And I would like to recover all columns for the rows that have a distinct GroupId
as follows (with a 'Date' desc order):
| Id | Message | GroupId | Date |
|-------------------------------|
| 1 | Hello | 1 | 1:00 |
| 3 | Hey | 2 | 2:00 |
| 4 | Dude | 3 | 3:00 |
我真的不在乎从分组的行(第一,第二...)中选择哪一行,只要是给定该组ID的唯一行即可.
I do not really care about which row is picked from the grouped ones (first, second...) as long as is the only one given that group Id.
到目前为止,我已经给出了以下代码,但是它没有执行应做的事情:
I have came out with the following code so far but it does not do what is supposed to:
List<XXX> messages = <MyRep>.Get(<MyWhere>)
.GroupBy(x => x.GroupId)
.Select(grp => grp.OrderBy(x => x.Date))
.OrderBy(y => y.First().Date)
.SelectMany(y => y).ToList();
推荐答案
这将为您提供每组一项:
This will give you one item per group:
List<dynamic> data = new List<dynamic>
{
new {ID = 1, Message = "Hello", GroupId = 1, Date = DateTime.Now},
new {ID = 2, Message = "Hello", GroupId = 1, Date = DateTime.Now},
new {ID = 3, Message = "Hey", GroupId = 2, Date = DateTime.Now},
new {ID = 4, Message = "Dude", GroupId = 3, Date = DateTime.Now},
new {ID = 5, Message = "Dude", GroupId = 3, Date = DateTime.Now},
};
var result = data.GroupBy(item => item.GroupId)
.Select(grouping => grouping.FirstOrDefault())
.OrderByDescending(item => item.Date)
.ToList();
//Or you can also do like this:
var result = data.GroupBy(item => item.GroupId)
.SelectMany(grouping => grouping.Take(1))
.OrderByDescending(item => item.Date)
.ToList();
如果您想控制 OrderBy
,则:
var result = data.GroupBy(item => item.GroupId)
.SelectMany(grouping => grouping.OrderBy(item => item.Date).Take(1))
.OrderByDescending(item => item.Date)
.ToList();
这篇关于选择所有列,但仅在linq中将其分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!