lambda expression join multiple tables with select and where clause

Question

I have three table many to many relationship I have joined the three table and select the value I want but now I need to select one row from the query result by where by specifying the id this is my three table

And this is the query using LINQ lambda expression :

DataBaseContext db = new DataBaseContext();

public ActionResult Index()
{

    var UserInRole = db.UserProfiles.
        Join(db.UsersInRoles, u => u.UserId, uir => uir.UserId,
        (u, uir) => new { u, uir }).
        Join(db.Roles, r => r.uir.RoleId, ro => ro.RoleId, (r, ro) => new { r, ro })
        .Select(m => new AddUserToRole
        {
            UserName = m.r.u.UserName,
            RoleName = m.ro.RoleName
        });

    return View(UserInRole.ToList());
}

the result will be like that using sql query

sql query

select * 
from UserProfile u join webpages_UsersInRoles uir on u.UserId = uir.UserId 
                   join webpages_Roles r on uir.RoleId = r.RoleId

result of the sql query

now i use anther sql query to filter the result of previews sql query by where and set the condition to where u.UserId = 1 to only give me back the user with the id 1 like that

select * 
from UserProfile u join webpages_UsersInRoles uir on u.UserId = uir.UserId 
                   join webpages_Roles r on uir.RoleId = r.RoleId 
where u.UserId = 1

and the result of this sql query

so how can i add the where clause to my lambda expression to give me the same result as the result of the sql query and thanks for any help

Kindly refer this http://stackoverflow.com/questions/1524813/convert-this-linq-expression-into-lambda — Ramselvaraj, Apr 07 '14 at 09:07

score 52 · Accepted Answer · edited Jun 29 '22 at 09:44

52

If I understand your questions correctly, all you need to do is add the .Where(m => m.r.u.UserId == 1):

var userInRole = db.UserProfiles.
    Join(db.UsersInRoles, u => u.UserId, uir => uir.UserId,
    (u, uir) => new { u, uir }).
    Join(db.Roles, r => r.uir.RoleId, ro => ro.RoleId, (r, ro) => new { r, ro })
    .Where(m => m.r.u.UserId == 1)
    .Select (m => new AddUserToRole
    {
        UserName = m.r.u.UserName,
        RoleName = m.ro.RoleName
    });

Hope that helps.

edited Jun 29 '22 at 09:44

live2

3,771
2
37
46

answered Apr 07 '14 at 09:16

Radu Pascal

1,275
14
18

in this part `u => u.UserId, uir => uir.UserId` **u.UserId** mean db.userProfiles.UserId ? – Ashkan Feb 21 '16 at 11:08

score 4 · Answer 2 · edited Jun 29 '22 at 09:44

4

I was looking for something and I found this post. I post this code that managed many-to-many relationships in case someone needs it.

var userInRole = db.UsersInRoles.Include(u => u.UserProfile).Include(u => u.Roles)
.Select (m => new 
{
    UserName = u.UserProfile.UserName,
    RoleName = u.Roles.RoleName
});

edited Jun 29 '22 at 09:44

live2

3,771
2
37
46

answered Oct 03 '17 at 12:45

Wilfredo

41
3

lambda expression join multiple tables with select and where clause

2 Answers2

Linked