lambda表达式使用select和where子句连接多个表 [英] lambda expression join multiple tables with select and where clause

问题描述

我有三个表，多对多关系，我已经加入了三个表并选择了我想要的值，但是现在我需要从查询结果中选择一行，通过指定ID来确定这是我的三个表.

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

这是使用LINQ lambda表达式的查询:

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

结果将类似于使用sql查询

sql查询

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

sql查询的结果

现在我使用花药sql查询按位置过滤预览sql查询的结果，并将条件设置为where u.UserId = 1以仅将ID为1的用户还给我

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

以及该sql查询的结果

所以我该如何在我的lambda表达式中添加where clause，以使我得到与sql查询结果相同的结果，并感谢您的帮助

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

推荐答案

如果我正确理解了您的问题，则只需添加.Where(m => m.r.u.UserId == 1):

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

希望有帮助.

这篇关于lambda表达式使用select和where子句连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！