I'm having problems coming up with the correct query to get the data out of my ERD. So in the image below is a simplified version for clarity.
So a user can join a group this relation is kept in the UserPerGroup table, a group can have events. now I want to be able to get all events (descriptions/ objects) for every group the user is part of. I'm using Linq in a MVC .NET project but don't mind if I get a raw (MS) SQL query.
EDIT: can the query below be converted to the following Linq format?
return db.Event. ... .ToList();
select e.id,e.[description] from [User] u
Inner join UserPerGroup upg on upg.userid=u.id
Inner join [event] e on e.groupid=upg.groupid
You can use simple LINQ join operator to correlate the entities like this (I'm guessing the names):
int userId = ...; var query = from e in db.Event join ug in db.UsersPerGroup on e.GroupId equals ug.GroupId where ug.UserId == userId select e; return query.ToList();