我如何使用Linq / Linq来执行此操作？

I have an audit table and I'm trying to use Linq to figure out how to grab all the people's more recent audit actions. I'm just not sure how.

我有一个审计表，我正在尝试使用Linq来弄清楚如何抓住所有人最近的审计行动。我只是不确定如何。

Sample Schema

Audit Table (technically it's actually a view)

审计表（从技术上讲，它实际上是一个视图）

AuditId   UserName   Action    CreatedOn
1         Bob        Action1   2000-01-01
2         Bob        Action2   2000-01-02
3         Bob        Action8   2000-01-03
4         Jane       Action1   2000-01-01
5         Bob        Action1   2000-01-05
6         Jane       Action3   2000-01-05
7         Andrew     Action4   2000-01-05
8         Siena      Action1   2000-01-06
9         Sarah      Action1   2000-01-07
10        Harry      Action6   2000-01-09

So, for the results, i'll be after...

所以，对于结果，我会追随......

5, Bob, Action1, 2000-01-05
6, Jane, Action3, 2000-01-05
7, Andrew, Action4, 2000-01-05
8, Siena, Action1, 2000-01-06
9, Sarah, Action1, 2000-01-07
10, Harry, Action6, 2000-01-09

Can anyone help, please?

有人可以帮帮忙吗？

(Oh, and if i need to index something, please suggest which column).

（哦，如果我需要索引某些内容，请建议哪一列）。

Thanks kindly :)

谢谢，亲切:)

2 个解决方案

#1

I think that would look something like this:

我认为这看起来像这样：

var query = from action in db.Actions
            orderby action.CreatedOn descending
            group action by action.UserName into groupedActions
            let mostRecent = groupedActions.First()
            select new { UserName = groupedActions.Key,
                         LatestId = mostRecent.AuditId,
                         LatestAction = mostRecent.Action,
                         LatestDate = mostRecent.CreatedOn };

As noted in the comments, this would work for LINQ to Objects, but it's possible that the grouping won't preserve order in SQL. Also the "mostRecent" record would probably give you everything you need. Here's another query addressing both points:

正如评论中所指出的，这适用于LINQ to Objects，但分组可能不会保留SQL中的顺序。此外，“最近”的记录可能会为您提供所需的一切。这是针对这两点的另一个查询：

var query = from action in db.Actions
            group action by action.UserName into groupedActions
            select groupedActions.OrderByDescending(a => a.CreatedOn).First();

Add indexes for CreatedOn and UserName.

为CreatedOn和UserName添加索引。

#2

Another simple way is:

另一个简单的方法是：

List<Audit> result = ctx.Audit.GroupBy(g => g.UserName)
                        .Select(t => t.OrderByDescending(o => o.CreatedOn)
                                      .Select(s => s)
                                      .First())
                        .ToList();

#1