How to select needed records in joining table using LINQ with tables which have many to many relatio-CodePudding

I have many to many relationship between entities user and group, I also have joining table GroupParticipants.

public class User
{
    public string Id {get; set;}

    public ICollection<GroupParticipant> Group { get; set;}
}

public class Group
{
    public int Id { get; set; }
    public ICollection<GroupParticipant> Participants { get; set; }
    
}

public class GroupParticipant
{
    public int GroupId { get; set; }
    public string ParticipantId { get; set; }

    public User Participant { get; set; }
    public Group Group { get; set; }
}

I need to select groups which user specified user did not join. I want to do something like:

string userId = 5;
var groupsAvailableToJoin = await _context.Groups
    .Where(group => group.Participants.Id != userId);

Thanks!

CodePudding user response：

A query like:

_context.Groups.Where(g =>
    !_context.GroupParticipants.Any(gp => gp.UserId == userId && gp.GroupId == g.I'd
);

Should translate to:

  SELECT * FROM Groups g
  WHERE NOT EXISTS(SELECT null FROM groupParticipants gp WHERE gp.UserId = 5 AND gp.GroupId = g.Id)

Which should be a reasonably performant way of getting you what you're looking for.. I'm sure that the GroupParticipants columns are indexed..

There are various ways to write this - if you find a two step approach easier to understand, it's effectively the same as:

var joined = _context.GroupParticipants.Where(gp => gp.UserId == 5).Select(gp => gp.GroupId).ToList();
var notJoined = _context.Groups.Where(g => !joined.Contains(g.Id));

This one translates as a NOT IN (list,of,groups,they,are,in) for a similar effect