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 !
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;
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
}
N+1 so I stick with mapping Models2DB 1 to 1 to have more control. Hence IQueryable and not List.