lambda expression join multiple tables with select and where clause
Solution 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
});
Hope that helps.
Solution 2
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
});
Related videos on Youtube
Fadi
Updated on October 04, 2020Comments
-
Fadi over 3 years
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
querysql
queryselect * 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
querynow i use anther
sql
query to filter the result of previewssql
query by where and set the condition towhere u.UserId = 1
to only give me back the user with the id 1 like thatselect * 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
queryso how can i add the where
clause
to my lambda expression to give me the same result as the result of thesql
query and thanks for any help-
Ramselvaraj about 10 yearsKindly refer this stackoverflow.com/questions/1524813/…
-
-
Ashkan about 8 yearsin this part
u => u.UserId, uir => uir.UserId
u.UserId mean db.userProfiles.UserId ?