sqlasp.net-mvclinqlinq-to-sql

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.

Thanks

Simplified ERD

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

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

which 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


Solution

  • 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();