LINQ select query

Question

I have this as my model;

What I'm looking for is a LINQ expression that can give me the UserActivity records, sorted by UserActivity.DateTime of all the users friends.

In other words, if a user has three friends, then I want the activity records of those three friends.

I've tried using .In and .Contains but my LINQ is pretty poor at the moment.

or can anyone suggest a differnt model that would give me the same results?

Answer 1

I don't know if this helps or not but it was on top of my head and I didn't even test it :-) Hope this helps. USER_ID is the user that you want to see his/her friends' activities.

from a in UserActivity
where 
  (
     from f in UserFriend 
     where f.Userid == USER_ID 
     select f.FriendUserId
  )
  .Distinct().ToList().Contains(a.UserId)
order by a.DateTime
select a;

Answer 2

If you have the user id of the user who you want the friend activities for something like this should work with you current schema:

var results = from user in aspnet_User 
              join friend in UserFriend on user.UserId equals friend.UserId
              join activity in UserActivity on activity.UserId equals friend.FriendUserId
              where user.UserId == someUserId
              orderby activity.DateTime ascending
              select activity;