如何在linq中使用union从子查询中获取记录

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

在 LINQ 中，您可能会这样：

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));

或者，使用联接：

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;

我怀疑这些都不会使用联合。您可以使用 LINQ 的联合，如下所示：

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));

...但这很麻烦。我可能会加入。