LINQ to Entities - Select all the User's friends and the Chat between them

Question

How can I Select all the friends of the currently logged in user and the Private Chats (Chat Id) between the user and their friends? I'm able to get the user's friends but I'm having trouble also getting the Chat between them.

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

Friendship table

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

User table

    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 table

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

Chat

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

Thank you

Answer 1

This query:

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

... loads the user friends with the friends corresponding chats which would include chats not with the current user.

With that domain structure for chats and friendships it looks rather tricky to try and link them up in a meaningful way. It is likely possible with a simple two-pass approach:

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

Chats are related to two or more users, and they are not restricted/linked to friendships.

Joe could be friends with Sam, Jane, and John and have the following chats active:

Chat 1: Joe <-> Sam

Chat 2: Joe <-> Jane

Chat 3: Joe <-> Jane <-> Sam

Chat 4: Joe <-> Frank

Chat 5: Joe <-> Frank <-> Sam

We'd want Chats 1, 2, 3, and 5 returned. There are no chats with John, and we don't care about the chat with Frank, but do care about the one with Frank & Sam since Sam is a friend. The goal would be to identify which chats that Joe is participating in with one or more of his friends. For each match we return the chat and any Friends currently also in that chat.

The caveat of the two-pass approach is that it assumes that the friend list would remain reasonably small, not large enough to exceed the IN() list that would be generated to get the matching Chats.