LINQ pivot on a single field

Question

I have three tables: User, UserRoles and Roles. UserRoles contains the association of the user to one or more roles.

I'm trying to write a LINQ query, using Entity Framework 6, that will produce the output of one user per row listing all the roles associated to the user, like the following:

USER      ROLES
John      Administrator, Supervisor
Chiara    Supervisor
Mark      Supervisor, Contributor

Currently, my query is the following, but obviously I get duplicated records depending by the number of roles associated with the user:

var users = (from u in db.Users.AsNoTracking()
             join ur in db.UserRoles.AsNoTracking() on u.UserId equals ur.UserId into urj
             from urc in urj.DefaultIfEmpty()
             group u by new
             {
                 u.UserId,
                 u.UserName,
                 urc.RoleName,
                 urc.RoleId
             } into grp
             orderby grp.Key.UserName
             select new
             {
                 grp.Key.UserId,
                 grp.Key.UserName,
                 RoleName = grp.Key.RoleName,
                 RoleId = grp.Key.RoleId
             });

Is there a way to write one single query, or two at most, or I need to split it? I'm trying to find the best performance and minimize the number of queries done to the database.

Try : RoleName = string.Join(",", grp.Select(x => x.RoleName).ToArray()), — jdweng, Jul 03 '20 at 15:06
@jdweng I'm using LInqToEntities (EF6): LINQ to Entities does not recognize the method 'System.String Join(System.String, System.String[])' method..... — Giox, Jul 03 '20 at 15:23
Where does the `Roles` table come into play and why don't you use navigation properties instead of joining? — Gert Arnold, Jul 03 '20 at 20:58

score 1 · Accepted Answer · answered Jul 07 '20 at 10:10

So there is a many-to-many relation between Users and Roles, and you want all (or some) Users, each with all their roles (or some of their roles).

Use the virtual ICollections!

Apparently you are using Entity Framework. If you have followed the conventions, you will have classes similar to the following:

class User
{
    public int Id {get; set;}
    public string name {get; set;}
    ...

    // every User has zero or more roles (many-to-many)
    public virtual ICollection<Role> Roles {get; set;}
}

class Role
{
    public int Id {get; set;}
    public string name {get; set;}
    ...

    // every Role is played by zero or more Users (many-to-many)
    public virtual ICollection<User> Users {get; set;}
}

It might be that you have different property names, but the most important part is the virtual ICollection<...>. This informs entity framework about the many-to-many relation between Users and Roles.

In entity framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the table (one-to-many, many-to-many, ...)

Entity framework knows the relation between the tables. If you use the collections, entity framework will automatically translate this in the proper (group-)joins.

Requirement Give me (some properties of) all Users, each with their Roles

var usersWithTheirRoles = dbContext.Users

    // only if you don't want all Users:
    .Where(user => user.City == "New York")

    .Select(user => new
    {
         // Select only the User properties that you plan to use:
         Id = user.Id,
         Name = user.Name,
         ...

         Roles = user.Roles
             // only if you don't want all Roles
             .Where(role => ...)
             .Select(role => new
             {
                 Id = role.Id,
                 Name = role.Name,
                 ...
             })
             .ToList(),
    });

Do the GroupJoin yourself

If you don't want to use the virtual ICollections, or your entity framework versions doesn't support this, consider to do the groupjoin yourself:

var result = dbContext.Users.GroupJoin(dbContext.UserRoles,

user => user.Id,                 // from every User take the Id
userRole => userRole.UserId,     // from every UserRole take the foreign key to User

// parameter resultSelector: for every user, and its zero or more userRoles,
// make one new object, containing the following properties:
(user, userRolesOfThisUser) => new
{
    Id = user.Id,
    Name = usr.Name,

    // for the Roles of this User: groupjoin all Roles with the UserRoles of this User
    Roles = dbContext.Roles.GroupJoin(userRolesOfThisUser,

        role => role.Id               // from every Role take the Id,
        userRole => userRole.RoleId,  // from every UserRole of this user take the foreign key

        // result selector: take every Role, and its zero or more UserRoles
        // that are also UserRoles of this User, to make one new Role:
        (role, userRolesOfThisRole) => new
        {
            Id = role.Id,
            Name = role.Name,
        })
        .ToList(),
});

In my opinion the virtual ICollection method looks way more elegant and natural.

score 0 · Answer 2 · answered Jul 03 '20 at 16:13

To do this with single query, you will need to use sub queries or CTE (see this answer). I would rather create a view with subquery that would return the desired data:

Create View ViewUserRoles
AS
SELECT 
    users.UserName AS [User]
    , ROLES = (Select RoleName + ',' From UserRoles Inner Join Roles On UserRoles.RoleId=Roles.RoleId Where UserId=users.UserId FOR XML PATH(''))
    FROM Users users
    Inner Join UserRoles userroles On users.UserId = userroles.UserId
    Group By users.UserName, users.UserId
GO

LINQ pivot on a single field

2 Answers2

Use the virtual ICollections!

Do the GroupJoin yourself