Linq to sql and join problem

Question

I have 3 tables:

USER
=====
USER_ID(PK)
FISRT_NAME
LAST_NAME
...

ROLES
======
ROLE_ID (PK)
NAME

USER_ROLES
==========
USER_ID(PK, FK)
ROLE_ID(PK, FK)

I want to extract all user data and all his roles (comma separated) into single row.
Sometging like this:

1 | John | Smith | Power user, Administrator
2 | John | Doe   | Guest

I don't know how to do this. Thank you for your help.

edit:
I tried something like this:

List<UserDTO> users = null;

using (CarShopDataContext dc = DB.GetContext())
{
    users = (from u in dc.Users
                select new UserDTO
                {
                    UserId = u.UserId,
                    Username = u.Username,
                    FirstName = u.FirstName,
                    LastName = u.LastName,
                    Roles = ""
                }).ToList();

    foreach (var user in users)
    {
        var roles = (from ur in dc.UserRoles
                        join r in dc.Roles on ur.RoleId equals r.RoleId
                        where ur.UserId == user.UserId
                        select r.Name).ToList();

        StringBuilder userRoles = new StringBuilder();
        for (int j = 0; j < roles.Count; j++)
        {
            userRoles.Append(roles[j]);

            if (j < roles.Count - 1)
                userRoles.Append(", ");
        }

        user.Roles = userRoles.ToString();
    }
}

return users;

Answer 1

SQL server doesn't have any aggregate operator to produce a CSV list so what you want is probably best achieved by querying the resulting data with Linq-To-Entities:

var rows =
    (from u in context.User
     join ur in context.UserRoles on u.User_ID equals ur.User_ID
     join r in context.Roles on ur.Role_ID equals r.Role_ID
     select new 
     {
         FirstName = u.First_Name,
         LastName = u.Last_Name,
         RoleName = r.Name
     }).AsEnumerable();

var roles =
    from r in rows
    group r by new {r.FirstName, r.LastName} into g
    select new
    {
        FirstName = g.Key.FirstName,
        LastName = g.Key.LastName,
        Roles = g.Select(x => x.RoleName).Aggregate((x, y) => x + "," + y)
    };