Return entity with latest timestamp matching a userId LINQ

Question

I am stuck on this. I have two tables: Users2Users and ActivityLog. I want to look up the list of Users2Users that are a user's friends and return each of their latest activity based on its Timestamp, then check what type it is.

Users2Users
int UserId
int TypeOfLink
int FriendId

ActivityLog
int Type
int UserId
DateTime Timestamp

The thing that seems to be troubling me is getting the ActivityLog entity rather than its Timestamp value.

My friendList query looks like this:

var friendList = (from u in db.Users2Users
where u.UserId == userId || u.FriendId == userId
&& u.TypeOfLink == 2 // confirmed as friend
orderby u.User.ScreenName ascending
select u).Distinct().ToList();

After that I was going to try a foreach, but how do I return the ActivityLog entity rather than the Timestamp?

// get their last activity
foreach (var user in domusers)
{
    var act = (from a in db.ActivityLogs
    where a.UserId == user.UserId
    select a.Timestamp).Max();
    // other stuff
}

Martin Liversage · Accepted Answer · 2012-08-10T16:49:15.887

You can order the entries by Timestamp and the pick the first element:

var act = db.ActivityLogs
  .Where(a => a.UserId == user.UserId)
  .OrderByDescending(a => a.Timestamp)
  .FirstOrDefault();

Or using query syntax:

var act = (from a in db.ActivityLogs 
where a.UserId == user.UserId
orderby a.Timestamp descending
select a).FirstOrDefault();

Note that if there are no elements in the source collection you will get a null result.

Using OrderByDescending is slightly ineffecient because it will sort all the activities. It is more efficient to perform a single pass over the collection and find the element with the latest Timestamp. You can do that using Aggregate. Here I assume that you are able to create a "null" Activity where the Timestamp property has the default (and minimal value) for DateTime:

var nullActivity = new Activity(); // nullActivity.Timestamp == DateTime.MinValue
var activity = db.ActivityLogs
   .Where(a => a.UserId == user.UserId)
   .Aggregate(
     nullActivity,
     (accumulate, value) => value.Timestamp >= accumulate.Timestamp ? value : accumulate
   );
if (activity != nullActivity)
  // The latest activity was found

If you can't create a "null" activity as the seed for Aggregate you can use an anonymous type to keep track of the latest timestamp and the associated activity but it is slightly more tedious to do.

Thanks for this. I think in your first bit of code the Where is missing the lambda? Where(a => a.UserId == user.UserId) — testpattern, Aug 10 '12 at 14:18
@testpattern: Thanks for pointing it out. It should be fixed now. — Martin Liversage, Aug 10 '12 at 14:30
Martin, I'll try this out a bit later and let you know. Thanks for your help. — testpattern, Aug 10 '12 at 16:44

Return entity with latest timestamp matching a userId LINQ

1 Answers1