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
This question is related to
c#
asp.net-mvc
linq
entity-framework
join
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
});
Source: Stackoverflow.com