LINQ query to fetch entities that include custom formatted field of associated entities?

Question

A bit rusty on LINQ I want to get a single result from related tables for a given user. See schema below. Each user has one or more roles. I want a list of usernames and a custom string that is a list of their roles in a format such as "Role1 - Role2 - Role3", where the values are the RoleNames associated with the UserRole/Role for that user.

Role
=====
RoleId
RoleCode
RoleName

UserRole
========
UserRoleId
RoleId
UserId

Users
======
UserId
UserName

Testing it out in LINQpad, I can get a list of usernames and their roles, but instead of the RoleName, I want a single field in the result to be a formatted string of ALL the users roles, as mentioned above.

Here is what I have now. How can I construct a list of the roles for each user?

from u in Users 
join ur in UserRoles on u.UserId equals ur.UserKey
join r in Roles on ur.RoleKey equals r.RoleId
select new { 
    u.UserId,
    u.UserName,
    r.RoleName
}

score 0 · Answer 1 · answered Jan 17 '14 at 15:37

Group the RoleNames over the UserNames and use String.Join to get the desired result.

from u in Users
join ur in UserRoles on u.UserId equals ur.UserKey
join r in Roles on ur.RoleKey equals r.RoleId
group r.RoleName by u.UserName into grp
select new {
  UserName = grp.Key,
  Roles = String.Join(" - ", grp)
};

This will not return the UserIds. If they're important for the result, you need to change the code to

.... join as above
group r.RoleName by new {u.UserId, u.UserName} into grp
select new {
  grp.Key.UserName,
  grp.Key.UserId,
  Roles = String.Join(" - ", grp)
};

This will create a grouping key containing both UserId and UserName, so you have that available in your select.

Edward · Accepted Answer · 2014-01-17T17:36:58.643

Add group by UserName to your LINQ query, and use string.Join to format the roles separated by "-".

You can test this in LINQPad -

var Roles = new [] {new{RoleId=1,RoleCode="SU",RoleName="Super User"},new{RoleId=2,RoleCode="PU",RoleName="Power User"}};
var Users = new [] {new{UserId=1,UserName="Bit Shift"},new{UserId=2,UserName="Edward"}};
var UserRoles = new [] {new{UserRoleId=1,RoleId=1,UserId=1},new{UserRoleId=2,RoleId=2,UserId=1},new{UserRoleId=3,RoleId=2,UserId=2}};

var userRoles = from u in Users 
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
select new
{ 
    u.UserId,
    u.UserName,
    r.RoleName
}
into userRole
group userRole by userRole.UserName into userGroups
select new{ UserName=userGroups.Key, Roles = string.Join(" - ", userGroups.Select(ug => ug.RoleName))};
userRoles.Dump();

LINQPad result

More succinct version, which includes UserId+UserName in result -

var userRoles = from u in Users 
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
group r by u into userGroups
select new{ User=userGroups.Key, Roles = string.Join(" - ", userGroups.Select(r => r.RoleName))};
userRoles.Dump();

LINQPad result 2

When executing against SQL database, you need to the query split into two parts, as String.Join is not supported by LINQ to SQL, like this -

   var userRoleGroups = (from u in Users 
    join ur in UserRoles on u.UserId equals ur.UserId
    join r in Roles on ur.RoleId equals r.RoleId
    group r by u into userGroups select userGroups)
    .ToList(); // This causes SQL to be generated and executed
    var userRoles = from userGroups in userRoleGroups select(new{ User=userGroups.Key, Roles = string.Join(" - ", userGroups.Select(r => r.RoleName))});
    userRoles.Dump();

Or try using Aggregate instead of String.Join, as you suggested, like this -

var userRoles = from u in Users 
join ur in UserRoles on u.UserId equals ur.UserId
join r in Roles on ur.RoleId equals r.RoleId
group r by u into userGroups 
select(new{ 
    User=userGroups.Key,
    Roles = userGroups.Select(s => s.RoleName).Aggregate((current, next) =>  current + " - " + next)});
userRoles.Dump();

When I switch over to using my tables instead of the arrays, LINQpad complains about the string join LINQ to Entities does not recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])' method, an... — bitshift, Jan 17 '14 at 16:40
OK, that is because String.Join cannot be converted into SQL directly. You need to do it in 2 steps. I will amend answer to show how... — Edward, Jan 17 '14 at 17:01
Aggregate is a good idea - I don't know whether LINQ to SQL will handle it - I will add that too, could you try it and tell me if it works? — Edward, Jan 17 '14 at 17:35
starange error Unable to cast object of type 'LINQPad.Internal.LINQPadDbConnection' to type 'System.Data.SqlClient.SqlConnection'. — bitshift, Jan 17 '14 at 18:06
My bad...it works now, not sure what LP was complaining about. Just fired up a new query tab. Only thing now is the User in the results. I just want the User Name and ID. How would I filter that 2nd block to include use the UserName — bitshift, Jan 17 '14 at 18:10
change over the final select to userGroups.Key.UserId, userGroups.Key.UserName — bitshift, Jan 17 '14 at 18:21
ya know, i wonder if SQLfunction.StringConvert would allow the 2 seperate blocks to be done in one? I think I have used this in the past when I ran into this. See this thread http://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities/3292773#3292773 The other option is to do this in a SQL procedure and include that procedure in the model. — bitshift, Jan 18 '14 at 13:05

LINQ query to fetch entities that include custom formatted field of associated entities?

2 Answers2