title: lambda expression join multiple tables with select and where clause
body: <p>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 <img src="https://i.stack.imgur.com/6gmcd.gif" alt=""> </p>
  
  <p>And this is the query using <code>LINQ</code> lambda expression :</p>
  
  <pre><code>DataBaseContext db = new DataBaseContext();
  
  public ActionResult Index()
  {
  
      var UserInRole = db.UserProfiles.
          Join(db.UsersInRoles, u =&gt; u.UserId, uir =&gt; uir.UserId,
          (u, uir) =&gt; new { u, uir }).
          Join(db.Roles, r =&gt; r.uir.RoleId, ro =&gt; ro.RoleId, (r, ro) =&gt; new { r, ro })
          .Select(m =&gt; new AddUserToRole
          {
              UserName = m.r.u.UserName,
              RoleName = m.ro.RoleName
          });
  
      return View(UserInRole.ToList());
  }
  </code></pre>
  
  <p>the result will be like that using <code>sql</code> query </p>
  
  <p><code>sql</code> query </p>
  
  <pre><code>select * 
  from UserProfile u join webpages_UsersInRoles uir on u.UserId = uir.UserId 
                     join webpages_Roles r on uir.RoleId = r.RoleId 
  </code></pre>
  
  <p>result of the <code>sql</code> query </p>
  
  <p><img src="https://i.stack.imgur.com/ZG2fg.gif" alt=""></p>
  
  <p>now i use anther <code>sql</code> query to filter the result of previews <code>sql</code> query by where and set the condition to <code>where u.UserId = 1</code> to only give me back the user with the id 1 like that </p>
  
  <pre><code>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
  </code></pre>
  
  <p>and the result of this <code>sql</code> query </p>
  
  <p><img src="https://i.stack.imgur.com/fBelz.gif" alt=""></p>
  
  <p>so how can i add the where <code>clause</code> to my lambda expression to give me the same result as the result of the <code>sql</code> query and thanks for any help   </p>