CODEWITHSUNDEEP
c#asp.net-mvclinqentity-frameworkjoin
Fadi
Fadi

Reputation: 2370

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

Upvotes: 29

Views: 172754

Answers (2)

Wilfredo
Wilfredo

Reputation: 41

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

Upvotes: 4

Radu Pascal
Radu Pascal

Reputation: 1337

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.

Upvotes: 54

Related Questions