Entity Framework Core左连接多表查询用户好友详情问题求助

问题原因 #

你原写法的核心问题是只关联了Connection表的User1字段到用户详情，没有区分当前用户在连接里是User1还是User2，导致始终取的是连接中User1的信息，而非好友的信息；同时直接同步阻塞ToListAsync().Result容易在ASP.NET Core环境触发线程死锁，也没有做空值防护。

正确实现代码 #

推荐异步版本（性能更优，无死锁风险）

internal async Task<ChatModel> GetChatHistoryAsync(int userID)
{
    ChatModel chat = new ChatModel();
    // 先查当前用户信息，做空值防护
    var currentUser = await this.UserCredentials.FirstOrDefaultAsync(m => m.Id == userID);
    if (currentUser == null)
    {
        throw new ArgumentException("用户不存在");
    }
    chat.UserName = currentUser.Username;
    chat.UserID = userID;

    var result = from conn in this.Connections
                 // 筛选当前用户参与的所有连接
                 where conn.User1 == userID || conn.User2 == userID
                 // 计算好友ID：当前用户是User1则好友是User2，反之则是User1
                 let friendId = conn.User1 == userID ? conn.User2 : conn.User1
                 // 关联好友的详情信息
                 join friendDetail in this.UserDetails on friendId equals friendDetail.Id
                 // 关联好友的账号信息取用户名
                 join friendCredential in this.UserCredentials on friendId equals friendCredential.Id
                 select new ChatWeb_MVC.Models.UserDetail
                 {
                     Id = friendId,
                     ConnectionID = conn.Id,
                     Username = friendCredential.Username,
                     FullName = friendDetail.FullName,
                     Avatar = GetAvatar(friendDetail.Avatar),
                     LastSeen = friendDetail.LastSeen,
                 };

    chat.Connections = await result.ToListAsync();
    return chat;
}

同步版本（适配原有方法签名）

internal ChatModel GetChatHistory(int userID)
{
    ChatModel chat = new ChatModel();
    var currentUser = this.UserCredentials.FirstOrDefault(m => m.Id == userID);
    if (currentUser == null)
    {
        throw new ArgumentException("用户不存在");
    }
    chat.UserName = currentUser.Username;
    chat.UserID = userID;

    var result = from conn in this.Connections
                 where conn.User1 == userID || conn.User2 == userID
                 let friendId = conn.User1 == userID ? conn.User2 : conn.User1
                 join friendDetail in this.UserDetails on friendId equals friendDetail.Id
                 join friendCredential in this.UserCredentials on friendId equals friendCredential.Id
                 select new ChatWeb_MVC.Models.UserDetail
                 {
                     Id = friendId,
                     ConnectionID = conn.Id,
                     Username = friendCredential.Username,
                     FullName = friendDetail.FullName,
                     Avatar = GetAvatar(friendDetail.Avatar),
                     LastSeen = friendDetail.LastSeen,
                 };

    chat.Connections = result.ToList();
    return chat;
}

内容的提问来源于stack exchange，提问作者Blue