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 stackoverflow.com/questions/1524813/…

Ramselvaraj
– Ramselvaraj

2014-04-07 09:07:28 +00:00
Commented Apr 7, 2014 at 9:07 — Ramselvaraj
– Ramselvaraj, Commented Apr 7, 2014 at 9:07

live2 · Accepted Answer · 2022-06-29 09:44:16Z

54

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, 2022 at 9:44

live2

4,3332 gold badges45 silver badges56 bronze badges

answered Apr 7, 2014 at 9:16

Radu Pascal

1,33714 silver badges19 bronze badges

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

1 Comment

Ashkan Over a year ago

in this part u => u.UserId, uir => uir.UserId u.UserId mean db.userProfiles.UserId ?

live2 · Accepted Answer · 2022-06-29 09:44:42Z

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, 2022 at 9:44

live2

4,3332 gold badges45 silver badges56 bronze badges

answered Oct 3, 2017 at 12:45

Wilfredo

413 bronze badges

Collectives™ on Stack Overflow

lambda expression join multiple tables with select and where clause

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related