How can i use Linq / Linq to Sql to do this?

Question

Hi folks,

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.

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 :)

Answer 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:

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.