How to get records from subquery using union in linq

Question

sql =  " SELECT * FROM userDetail ";
sql += " WHERE userId IN ";
sql += " (SELECT friendId FROM userFriends ";
sql += " WHERE approvalStatus='True' AND userId=" + userId;
sql += " UNION";
sql += " SELECT userId FROM userFriends ";
sql += " WHERE approvalStatus='True' AND friendId=" + userId + ")"; 

Answer 1

In LINQ, you could be something like:

var approvedUsers = db.UserFriends.Where(p => p.ApprovalStatus == "True");
var userIds = from p in approvedUsers
              where p.UserId == userId || p.FriendId = userId
              select p.UserId;

var friendsAndUser = db.UserDetails
                       .Where(detail => userIds.Contains(detail.UserId));

Alternatively, use a join:

var query = from user in db.UserFriends
            where p.ApprovalStatus == "True"
            where p.UserId == userId || p.FriendId == userId
            join detail in db.UserDetails on user.UserId equals detail.UserId
            select detail;

I suspect neither of these would use a union. You could use a union with LINQ, like this:

var approvedUsers = db.UserFriends.Where(p => p.ApprovalStatus == "True");
var userIds = from p in approvedUsers
              where p.UserId == userId
              select p.UserId;
var friendIds = from p in approvedUsers
                where p.FriendId = userId
                select p.UserId;
var allIds = userIds.Union(friendIds);
var friendsAndUser = db.UserDetails
                       .Where(detail => userIds.Contains(detail.UserId));

... but that's a lot of fuss. I'd probably go with the join.