Linq: multiple sub joins or sub-queries

I am pretty sure I can get the results I want from a single Linq statement, I am just not able to find it by myself! This is what I have so far:

var query = from uir in Aspnet_UsersInRoles
        join r in Aspnet_Roles
        on uir.RoleId equals r.RoleId 
        select new
        {
            UserId = uir.UserId,
            RoleName = r.RoleName
        };

var query2 = from q in query
        group q by q.UserId into roles
        select new 
        {
            UserId = roles.Key,
            Roles = roles.Distinct()
        };

var query3 = from u in Aspnet_Users
        join q in query2
        on u.UserId equals q.UserId
        select new
        {
            UserId = u.UserId,
            UserName = u.UserName,
            Roles = q.Roles
        };

Expected Results:

and that is not even correct: I don't really need to have UserId in the 'Roles' list/IEnumerable, just a list of the RoleName(s) for each UserId.

I am using asp.net membership sql tables and basically I want to get a lis开发者_运维百科ting of users including their Roles.

• Each user might have multiple roles.
• Users might have no Role at all (and should still show up on results)

Can you please help me or point me in the right direction on how to get the same results from a single Linq statement?

PS. Yes, I am a Linq newbie and a SQL veteran, that might be obscuring my thoughts.

EDIT

Ok, I think I am getting to it:

var q = from u in Aspnet_Users
    from up in UserProfiles.Where( _up => _up.UserId == u.UserId ).DefaultIfEmpty()
    from uir in Aspnet_UsersInRoles.Where( _uir => _uir.UserId == u.UserId ).DefaultIfEmpty()
    from r in Aspnet_Roles.Where( _r => _r.RoleId == uir.RoleId).DefaultIfEmpty()
    group r.RoleName by new     {
                                u.UserId, 
                                u.UserName, 
                                up.FirstName, 
                                up.LastName, 
                                u.LastActivityDate
                                } into g
    select new 
    {
        userID = g.Key.UserId,
        firstName = g.Key.FirstName,
        lastName = g.Key.LastName,
        userName = g.Key.UserName,          
        lastActivityDate = g.Key.LastActivityDate,
        roles = g.Distinct()
    };

What do you think? (I added info from my own UserProfiles table, 1:1 relation w/ Aspnet_Users)

---

I believe this is equivalent.

var query = from userRole in Aspnet_UsersInRoles
            join role in Aspnet_Roles on userRole.RoleId equals role.RoleId 
            group role.RoleName by userRole.UserId into roles
            join user in Aspnet_Users on roles.Key equals user.UserId
            select new
            {
                user.UserId,
                user.UserName,
                Roles = roles.Distinct(),
            };

See if this works out.