皇宫的实体选择的所有用户和朋友聊天他们之间

Question 1

我怎么可以选择的所有朋友的目前登录用户和私人聊天(聊天Id)用户之间和他们的朋友？我能够得到用户的朋友但我有麻烦也得到聊天在他们之间。

            // Select all the User's friends and the chat (Id) between them
            var friends = await _context.Friendships // Get the Friendships
                .Include(x => x.Friend).ThenInclude(x => x.ChatUsers).ThenInclude(x => x.Chat)
                .Where(x => x.Status == StatusCode.Accepted && x.ApplicationUserId == userId)
                .Select(x => x.Friend)
                .ToListAsync();

友谊表

    public class Friendship
    {
        // The primary keys/foreign keys of the associated tables
        public string ApplicationUserId { get; set; }
        public string ApplicationFriendUserId { get; set; }

        // Reference to the user that has the friend
        public User ApplicationUser { get; set; }

        // Reference to the friend
        public User Friend { get; set; }

        // The status of the friendship
        public StatusCode Status { get; set; }
    }

用户表

    public class User : IdentityUser
    {
        // Reference to all user's chats
        public ICollection<ChatUser> ChatUsers { get; set; }

        // Reference to all the user's friendships
        public ICollection<Friendship> UsersFriendships { get; set; }

        // Reference to all the friend's friendships (their point of view)
        public ICollection<Friendship> FriendsFriendships { get; set; }
    }

ChatUser表

    public class ChatUser
    {
        // The primary key/foreign keys of the associated tables
        public int ChatId { get; set; }
        public string UserId { get; set; }

        // The role that the User can be
        public UserRole Role { get; set; }

        // Reference to the chat
        public Chat Chat { get; set; }

        // Reference to the user
        public User User { get; set; }
    }

聊天

    
    public class Chat
    {
        // The primary key
        public int Id { get; set; }

        // The chat's name
        public string Name { get; set; }

        // The chat type, e.g room, private
        public ChatType Type { get; set; }

        // Reference to all the Chat's Users
        public ICollection<ChatUser> ChatUsers { get; set; }
    }

谢谢你

Question 2

这个查询:

var friends = await _context.Friendships // Get the Friendships
    .Include(x => x.Friend).ThenInclude(x => x.ChatUsers).ThenInclude(x => x.Chat)
    .Where(x => x.Status == StatusCode.Accepted && x.ApplicationUserId == userId)
    .Select(x => x.Friend)
    .ToListAsync();

...负荷的用户朋友的朋友应聊天，其中将包括聊天不与当前用户。

这域结构对于对话和友谊它看起来相当棘手的尝试，并将它们连接在一个有意义的方式。很可能可能用一个简单的两个通的方法：

var friendIds = _context.Users
    .Where(x => s.UserId == userId)
    .SelectMany(x => x.UsersFriendships.Where(f => f.Status == StatusCode.Accepted).Select(f => f.ApplicationFriendUserId))
    .ToList(); // Get all accepted Friend IDs.


 var chats = _context.Chats
     .Where(x => x.ChatUsers.Any(cu => cu.UserId) && x => x.ChatUsers.Any(cu => friendIds.Contains(cu.UserId)
     .Select(x => new 
     {
         Chat = x,
         Friends = x.ChatUsers.Where(cu => friendIds.Contains(cu.UserId)).Select(cu => cu.User).ToList()
      }).ToList();

聊天记录有关的两个或多个用户，他们不受限制/与友谊。

乔可能的朋友山姆，珍妮和约翰和具有以下聊天的活动：

聊天1:乔<->Sam

聊天2:乔<->Jane

聊天3:乔<->简<->Sam

聊天4:乔<->弗兰克

聊天5:乔<->弗兰克<->Sam

我们想聊天的1、2、3和5返回。还有无聊天与约翰,和我们不关心聊天弗兰克，但是不关心的一个弗兰克&Sam因为萨姆的一个朋友。我们的目标是确定其聊Joe正在参与一个或多个他的朋友。对每个匹配我们回聊天和任何朋友，目前还在聊天。

需要说明的两个通的办法是假设的朋友列表将仍然相当小，不足够大的超过 IN() 列表中，将产生得到匹配的对话。

Steve Py · Answer 1 · 2021-11-23T04:50:49