How can I merge two columns to create a single unique recordset (linq or SP)...

... and exclude the search parameter from the results?

I am trying to create a friends list.

I have a MemberMember table, where there are two columns.

One column is the person who requested friendship. The other column is the respondee.

Sometimes the user is the requestee and sometimes they are the respondee so their ID can end up being in both columns. If a user wants to see his friends, I need a single set of records with unique friends only.

My MemberMember table has the following columns that need to be merged into unique result set.

FromMemberId
ToMemberId


FYI, I would be okay doing this as a stored proc or in LINQ.... something like this?


SELECT CASE WHEN (a.FromMemberId = @UserId) THEN a.ToMemberId ELSE a.FromMemberId END AS UserId, MemberMemberId,
UserName, FirstName + ' ' + LastName as friendfullname, ProfileImage as friendProfilePhoto, CreatedOn as dateRequestMade
FROM pv_MemberMember a
INNER JOIN aspnet_Users b ON a.FromMemberId = b.UserId
INNER JOIN pv_Member c on b.UserId = c.UserId
WHERE a.FromMemberId = @UserId OR a.ToMemberId = @UserId AND a.IsApproved = 1 AND a.IsDeleted = 0