linq左连接只包含匹配值的问题

Question

我有以下代码

public async Task<List<UserBasicViewModel>> FindUsersWithFriendshipStatusAsync(
        Guid userId, 
        string searchValue, 
        int skip, 
        int take)
    {
        var db = DbMakeLocalInstance;
        var userQuery = 
            from user in db.Users
            where (user.FirstName.StartsWith(searchValue)
                   || user.LastName.StartsWith(searchValue)
                   || user.Name.StartsWith(searchValue)
                   || user.School.StartsWith(searchValue)
                   || user.SchoolClass.StartsWith(searchValue))
                   && user.UserId != userId
            join status in db.Friendship on user.UserId equals status.FriendUserId into fstatus
            from status in fstatus.DefaultIfEmpty()
            where status.UserId == userId
            select new {User = user, Status = status};
        var query2 = await (userQuery.OrderBy(u => u.User.Name)
            .Skip(skip)
            .Take(take)
            .ToListAsync()
            .ConfigureAwait(continueOnCapturedContext: false));
        var users = query2.Select(u => new UserBasicViewModel(u.User, u.Status)).ToList();
        return users;
    }

，它应该给我所有匹配搜索查询的用户和友谊状态，如果它与提供的userId存在。

问题在于，它只返回当前userId与搜索查询中匹配的用户之间已经存在关系的用户。我想要的用户匹配搜索查询没有任何匹配的友谊以及。

请帮忙!

Answer 1

你可以试试:

var userQuery = 
        from user in db.Users
        where (user.FirstName.StartsWith(searchValue)
               || user.LastName.StartsWith(searchValue)
               || user.Name.StartsWith(searchValue)
               || user.School.StartsWith(searchValue)
               || user.SchoolClass.StartsWith(searchValue))
               && user.UserId != userId
        select new {
            User = user,
            // I'm using single because I'm guessing there's only one match
            Status = db.Friendship
                .Where(f => f.FriendUserId == user.UserId && f.UserId == userId)
                .SingleOrDefault(),
        };

Answer 2

您可以检查状态之间是否相等。UserId和左连接前的UserId。

    from user in db.Users
    where (user.FirstName.StartsWith(searchValue)
           || user.LastName.StartsWith(searchValue)
           || user.Name.StartsWith(searchValue)
           || user.School.StartsWith(searchValue)
           || user.SchoolClass.StartsWith(searchValue))
           && user.UserId != userId
    join status in db.Friendship on new {user.UserId, searchUserId = userId} equals 
                                    new {UserId = status.FriendUserId, searchUserId = status.UserId} into fstatus
    from status in fstatus.DefaultIfEmpty()
    select new {User = user, Status = status};