Linq Many to many in Entity Framework core

.net-core asp.net-core-2.1 c# entity-framework entity-framework-core

Question

I have classes for building many to many relationships.

public class Chat 
{
    [Key]
    public Guid Id { get; set; }
    public ICollection<ApplicationUserChat> UserChats { get; set; }
}

public class ApplicationUserChat
{
    [Required]
    public string UserId { get; set; }
    public ApplicationUser User { get; set; }

    public Guid ChatId { get; set; }
    public Chat Chat { get; set; }
}

public class ApplicationUser : IdentityUser
{        
    public ICollection<ApplicationUserChat> UserChats { get; set; }       
}

For example, I have a user1 and user2

  1. I need to Select chats for user1 where chat contains a user2
  2. I need to select chat for user1 where chat contains a user2 and not contains another users (like one chat contains only 2 participants)

    How I can do this in EF Core via one request to DB?

1
0
1/29/2019 9:41:36 AM

Accepted Answer

If it is acceptable to perform 2 queries to database to get data for both cases you can use following queries

//chats that contain user1 and user2
var chats1 = context
    .Chats
    .Where(c => c.UserChats.Any(u => u.UserId == userId1))
    .Where(c => c.UserChats.Any(u => u.UserId == userId2))
    .ToList();

//chats that contain user1 and user2 and they are only users in chat
var chats2 = context
    .Chats
    .Where(c => c.UserChats.Any(u => u.UserId == userId1))
    .Where(c => c.UserChats.Any(u => u.UserId == userId2))
    .Where(c => c.UserChats.Count() == 2)
    .ToList();

If you want to retrieve all data in 1 request you can declare new class which holds needed information about chat

public class ChatDto
{
    public Guid Id { get; set; }

    public int UserCount { get; set; }
}

and perform following request

var chats = context
    .Chats
    .Where(c => c.UserChats.Any(u => u.UserId == userId1))
    .Where(c => c.UserChats.Any(u => u.UserId == userId2))
    .Select(c => new ChatDto
    {
        Id = c.Id,
        UserCount = c.UserChats.Count()
    })
    .ToList();

Therefore you will have chats that contain user1 and user2 and total count of users in chat.

0
1/29/2019 11:05:56 AM

Popular Answer

 var chats = user1.UserChats.Select(uc => uc.ChatId).Distinct();
 var result1 = context.ApplicationUserChat
       .Where(a => a.UserId == user2.Id && chats.Contains(a.ChatId))
       .Select(a => a.Chat)
       .ToList();
 var result2 = results1.Except(context.ApplicationUserChat
       .Where(a => a.UserId != user2.Id 
                   && a.UserId != user2.Id 
                   && chats.Contains(a.ChatId))
       .Select(a => a.Chat)
       .Distinct())
       .ToList();

//loading... short version using/if you already have the users lists:

 var result1 = user1.UserChat.Intersect(user2.UserChats);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow