title: LINQ to SQL multiple tables left outer join w/condition in 'on' clause
body: <pre><code>SELECT u.username, COUNT(r.id)
  FROM users u
           LEFT JOIN userroles ur ON u.id = ur.userid
           LEFT JOIN roles r ON ur.roleid = r.id AND r.name = 'Managers'
  GROUP BY u.username
  ORDER BY u.username
  </code></pre>
  <p>The goal is very simple, the above SQL runs fine, now I need to figure out how to convert it into LINQ code.  I have the left joins working, the two things I don't know how to do is the count and the r.name = 'Managers'.  Here is what I have so far, how do I finish it off?</p>
  <pre><code>var result =
      from user in _context.Users
      join userRole in _context.UserRoles on user.Id equals userRole.UserId into userUserRoleGroup
      from u in userUserRoleGroup.DefaultIfEmpty()
      join role in _context.Roles
          on u.RoleId equals role.Id into roleUserRoleGroup
      from r in roleUserRoleGroup.DefaultIfEmpty()
      select new UserRole { Username = user.UserName, RoleName = r.Name };
  </code></pre>