lordvlad30 lordvlad30 - 1 year ago 144
SQL Question

SQL query with linq in ASP.NET MVC project

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.


Simplified ERD

EDIT: can the query below be converted to the following Linq format?

return db.Event. ... .ToList();

wich would return an IEnumbarable?

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
Where u.id=[USER_ID_VALUE]

query from Rohit Padma

Answer Source

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();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download