How to use join to make this queries cleaner?

Question

I have the feeling that using joins could make this cleaner

public override string[] GetRolesForUser(string username)
{
   using (TemplateEntities ctx = new TemplateEntities())
   {
      using (TransactionScope tran = new TransactionScope())
      {
         int userId = (from u in ctx.Users
                       where u.UserName == username
                       select u.UserId).Single();
         int[] roleIds = (from ur in ctx.UserInRoles
                          where ur.UserId == userId
                          select ur.RoleId).ToArray();
         string[] roleNames = (from r in ctx.Roles
                               where roleIds.Contains(r.RoleId)
                               select r.RoleName).ToArray();
         tran.Complete();
         return roleNames;
      }
   }
}

Answer 1

You should be able to use the navigation properties to follow the relations instead of using the primary keys (Entity Framework will join behind the scenes for you)

If you have (and need) UserInRoles because there are other properties defined on the junction table, you can use:

return (from u in cts.Users
        from ur in u.UserInRoles
        from r in ur.Roles
        select r.roleName).ToArray();

Otherwise make sure the N-M relation is mapped as such, and don't map the junction table. Then you can just use:

return (from u in cts.Users
        from r in u.Roles
        select r.roleName).ToArray();

Answer 2

I'm not a c# guy, but essentially you would want to do

select u.userId, ur.roleId, r.roleName 
from Users u, UserInRoles ur, Roles r 
where u.userId = ? and ur.userId = u.userId and r.roleId = ur.roleId; 

You can also use the in syntax if you opt for nested queries. ie: where user_id in (select userId from UserInRoles)