How Do I Join Records In LINQ?

Question

Hi.

I am new to LINQ and am trying to sort this one out. I am trying to grab records for multiple accounts (multiple account ids) and sort the results according to the date.

What I have currently is putting the results in a list such that multiple rows / account are contained in each list item.

This is close but not exactly what I want. How do I return results such that all results (that is, all the records associated with each account) are sorted together by date and returned as such.

I imagine that this is some sort of LINQ join but I am not sure what the syntax would be.

public List<StatusUpdate> GetFriendStatusUpdatesByAccountId(Int32 accountId)
        {
            List<StatusUpdate> result;

            List<Friend> friends =
                _friendRepository.GetFriendsByAccountId(accountId);

            using (WorkbookDataContext dc = _conn.GetContext())
            {
                IEnumerable<StatusUpdate> statusUpdates = null;

                foreach (Friend friend in friends)
                {
                    Friend friend1 = friend;

                    statusUpdates = from su in dc.StatusUpdates
                                    where su.AccountId == friend1.MyFriendsAccountId
                                    orderby su.CreateDate descending
                                    select su;
                }

                if (statusUpdates != null) result = statusUpdates.ToList();
            }

            return result;
        }

Thanks in advance.

EDIT:

Thanks for your suggestion. I have reworked it a little bit in order to have access to OrderByDescending but can't seem to figure out the "cannot resolve symbol" problem for friend and su (shown below in CAPS). I am wondering if you were assuming that "friend in friends" was accessing Friend as the LINQ table (which would give us access to MyFriendsAccountId) and not List (which does not give us direct access to MyFriendsAccountId? Anyway, I am a LINQ newb so, maybe I am barking up the wrong tree. At any rate, FRIEND in friends and SU are throwing the "cannot resolve symbol error" and I'd appreciate suggestions. Thanks a ton for your feedback...

public List<StatusUpdate> GetFriendStatusUpdatesByAccountId(Int32 accountId)
        {
            List<StatusUpdate> result;

            using (WorkbookDataContext dc = _conn.GetContext())
            {
                List<Friend> friends =
                _friendRepository.GetFriendsByAccountId(accountId);

                IEnumerable<StatusUpdate> statusUpdates = 
                    from su in dc.StatusUpdates
                    join friend in friends
                    on FRIEND.MyFriendsAccountId
                    equals SU.AccountId
                    select su;

                    result = 
                        statusUpdates.OrderByDescending
                        (su => su.CreateDate).ToList();
            }

            return result;
        }

Answer 1

Hi, I'm a little confused by what you're trying to do, your code seems to overwrite the statusUpdates variable inside the foreach loop and by the look of it, result will always get you the sorted status updates for the LAST friend in the friends list.

If I'm understanding your question correctly, you have a many-to-one relationship between Friend and Account ID, and each Friend has a number of StatusUpdate, and you want to get all the StatusUpdate associated with all the Friends that share the same Account ID, and have them sorted by CreatedDate descending?

If that's the case, I think this might be what you're looking for:

public List<StatusUpdate> GetFriendStatusUpdatesByAccountId(Int32 accountId)
{
    List<StatusUpdate> result;

    List<Friend> friends = _friendRepository.GetFriendsByAccountId(accountId);

    using (WorkbookDataContext dc = _conn.GetContext())
    {
        var statusUpdates = 
           from su in dc.StatusUpdates
           join friend in friends on su.AccountId equals friend.MyFriendsAccountId
           select su;

        return statusUpdates.OrderByDescending(su => su.CreateDate)
                            .ToList();
    }
}

You can equally do the join using lambda expressions, but I usually favor the linq query syntax when it comes to joins as they are cleaner.

Hope this gives you what you want, if you're looking for some more examples on doing joins in linq, I've got a few examples here.