Entity Framework Many to Many query

Question

I want to write a simple query, but there are some problems. I have 2 tables M to N:

Users -> Events.

I want to get all users of a specific event (get this event by eventId).

public IQueryable<User> GetUsersByEventId(int eventId)
{
    IQueryable<User> query = this.Context.Users.AsQueryable();

    return query.Where(x => x.Events.SingleOrDefault(e => e.EventId == eventId)); ??
}

Something is missing and I dont know what, can someone help me? Thanks a lot !

Answer 1

If I understand you correctly (adding your models would help), I think you want Any

public IQueryable<User> GetUsersByEventId(int eventId)
{
      return Context.Users
                    .Where(u => u.Events.Any(e => e.EventId == eventId));
}

This should return all users who have any event matching the given id.

Note: If you set up your relationships correctly, you should be able to get this directly from the Event.

public class Event
{
    ...
    public virtual ICollection<User> Users { get; set; }
}

So then, you'd get the Event by id and access it's user collection.

var evt = repo.GetEventById(id);
var users = evt.Users;

Answer 2

I suggest you do that in your Event model itself. AFAIK you are using Event, User and EventUsers tables which is standard stuff for many2many.

public class Event
{
    public int Id { get; set; }
    // ...
    public virtual ICollection<EventUsers> EventUsers { get; set; } // This is table that holds EventId, UserId (many2many)
    public IQueryable<User> Users { get { return this.EventUsers.Select(x => x.User); } } // Get all users that are in this event
}