Entity Framework - writing query using lambda expression

Question

Hi, I have just started Entity Framework & linq and write this query

 var query = from rp in db.UM_RolePermission
             where (from ru in db.UM_RoleUser where ru.UM_User.UserID == userId select ru.RoleID).Contains(rp.RoleId)
                   select rp;

above is working fine and fullfill my need, however I am trying to write this same using lambda expression to understand that as well.

I have tried himself and write this but not able to complete

var query1 = db.UM_RolePermission.Where(rp => (from ru in db.UM_RoleUser where ru.UM_User.UserID == userId select ru.RoleID).Contains(rp.RoleId));

Can anyone complete this?

RelationShip: UM_RoleUser and UM_User

Thanks

Answer 1

I going to jump ahead and assume you've defined a relationship between RolePermission and RoleUser in a many-to-many relationship? That will make your life a lot simpler.

var query1 = db.UM_RoleUser
    .Where(ru => ru.UserId == userID)
    .SelectMany(rp => rp.RolePermissions);

Of course, this depends on how you've set up your relationships.

Answer 2

var query = db.UM_RolePermission
            .Where(rp => db.UM_RoleUser
                         .Where(ru => ru.UM_User.UserID == userId)
                         .Select(ru => ru.RoleID)
                         .Contains(rp.RoleId))