Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

lambda expression join multiple tables with select and where clause

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

like image 954
Fadi Avatar asked Apr 07 '14 08:04

Fadi


2 Answers

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.

like image 138
Radu Pascal Avatar answered Nov 13 '22 08:11

Radu Pascal


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
});
like image 36
Wilfredo Avatar answered Nov 13 '22 07:11

Wilfredo