title: LINQ to Entities - Select all the User's friends and the Chat between them
body: <p>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.</p>
  <pre><code>            // Select all the User's friends and the chat (Id) between them
              var friends = await _context.Friendships // Get the Friendships
                  .Include(x =&gt; x.Friend).ThenInclude(x =&gt; x.ChatUsers).ThenInclude(x =&gt; x.Chat)
                  .Where(x =&gt; x.Status == StatusCode.Accepted &amp;&amp; x.ApplicationUserId == userId)
                  .Select(x =&gt; x.Friend)
                  .ToListAsync();
  </code></pre>
  <p><strong>Friendship table</strong></p>
  <pre><code>    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; }
      }
  </code></pre>
  <p><strong>User table</strong></p>
  <pre><code>    public class User : IdentityUser
      {
          // Reference to all user's chats
          public ICollection&lt;ChatUser&gt; ChatUsers { get; set; }
  
          // Reference to all the user's friendships
          public ICollection&lt;Friendship&gt; UsersFriendships { get; set; }
  
          // Reference to all the friend's friendships (their point of view)
          public ICollection&lt;Friendship&gt; FriendsFriendships { get; set; }
      }
  </code></pre>
  <p><strong>ChatUser table</strong></p>
  <pre><code>    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; }
      }
  </code></pre>
  <p><strong>Chat</strong></p>
  <pre><code>    
      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&lt;ChatUser&gt; ChatUsers { get; set; }
      }
  </code></pre>
  <p>Thank you</p>