SQL query with linq in ASP.NET MVC project

Question

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

Ivan Stoev · Accepted Answer · 2016-07-10 18:23:59Z

1

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

answered Jul 10, 2016 at 18:23

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lordvlad30 Over a year ago

Thanks, I did not know this, I only saw the basic around Linq. This will help me a lot to solve future problems!

Rohit Padma · Accepted Answer · 2016-07-10 17:22:03Z

0

Hope this raw SQL query helps.

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]

answered Jul 10, 2016 at 17:22

Rohit Padma

6035 silver badges15 bronze badges

2 Comments

lordvlad30 Over a year ago

would wanne give you +1 for the query but my reputation is to low. But thanks again!

Rohit Padma Over a year ago

Its okay :) hope it helped.

Collectives™ on Stack Overflow

SQL query with linq in ASP.NET MVC project

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related