How to group by multiple columns and place contents of one field into a list of strings?

Question

After multiple joins I'm trying to group by two fields then place the contents of one field into a concatenated list of strings. For example if I had the following records...

--------------------------------------------------
|*Organization*| *Module*   | *Roles*            |
--------------------------------------------------
| Seattle      | Schools    | Superintendent     |  
--------------------------------------------------
| Seattle      | Food       | Director           |  
--------------------------------------------------
| Seattle      | Food       | Cook               |  
--------------------------------------------------
| Sarasota     | Staff      | Secretary          |  
--------------------------------------------------

would be come this....

--------------------------------------------------
|*Organization*| *Module*   | *Roles*            |
--------------------------------------------------
| Seattle      | Schools    | Superintendent     |  
--------------------------------------------------
| Seattle      | Food       | Director, Cook     |  
--------------------------------------------------
| Sarasota     | Staff      | Secretary          |  
--------------------------------------------------

Here is my query so far. As you can see I'm having trouble grouping by two items and concatenating the string of Roles.

var userAccountDetails = from account in _model.Accounts
                      join userRight in _model.UserRights on account.AccountId equals userRight.AccountId
                      join role in _model.Roles on userRight.RoleId equals role.Id
                      join organization in _model.Organizations on userRight.OrganizationId equals organization.Id
                      join module in _model.Modules on role.ModuleId equals module.Id
                      where (account.IsApproved == true && userRight.Status == 1 && account.AccountId == accountId)
                      group new {account, userRight, role, organization, module} by new {OrgId = organization.Id, ModId = module.Id} into grp
                      select new UserAccountDetails
                      {
                          AccountId = account.AccountId,
                          OrganizationId = organization.Id,
                          OrganizationName = organization.Name,
                          ModuleId = module.Id,
                          ModuleName = module.Name
                          Roles = // needs to be a concatenated list of role.label strings};

And this the object I'm trying to populate when all the joining and grouping is done...

    public class UserAccountDetails
{
    public int AccountId { get; set; }
    public int OrganizationId { get; set; }
    public string  OrganizationName { get; set; }
    public int ModuleId { get; set; }
    public string ModuleName { get; set; }
    public string Roles { get; set; }
}

So after having some trouble with string.Join method I am now trying to get all the Roles into an Enumerable and construct the string after. Howerver I'm unsure how to create the collection of Roles.

        public List<UserAccountDetails> GetUserAccountDetails(int accountId)
    {
        var userDetais = from account in _model.Accounts
                                 join userRight in _model.UserRights on account.AccountId equals userRight.AccountId
                                 join role in _model.Roles on userRight.RoleId equals role.Id
                                 join organization in _model.Organizations on userRight.OrganizationId equals organization.Id
                                 join module in _model.Modules on role.ModuleId equals module.Id
                                 where (account.IsApproved == true && userRight.Status == 1 && account.AccountId == accountId)
                                 group new { account, userRight, role, organization, module } by new { OrgId = organization.Id, ModId = module.Id } into grp
                                 select new
                                 {
                                     AccountId = grp.Select(x => x.account.AccountId).FirstOrDefault(),
                                     OrganizationId = grp.Key.OrgId,
                                     OrganizationName = grp.Select(x => x.organization.Name).FirstOrDefault(),
                                     ModuleId = grp.Key.ModId,
                                     ModuleName = grp.Select(x => x.module.Name).FirstOrDefault(),
                                     Roles = grp.Select(x => _) // unsure how to get all the roles i need
                                 };

        var userAccountDetails = userDetais.AsEnumerable()
                              .Select( x => new UserAccountDetails
                              {
                                  AccountId = x.AccountId,
                                  OrganizationId = x.OrganizationId,
                                  OrganizationName = x.OrganizationName,
                                  ModuleId = x.ModuleId,
                                  ModuleName = x.ModuleName,
                                  RolesString = string.Join(",", x.Roles)
                              }).ToList();

        return userAccountDetails;         

    }

The class is defined for Roles as a string. So you have to use string.Join(",", ARRAY) — jdweng, Oct 14 '16 at 16:49
`Roles = string.Join(",", role.label)`? Or whatever your array/list of labels is? — Geoff James, Oct 14 '16 at 16:49
@GeoffJames, string.Join needs `IEnumerable` or an `Array`, not single value — Mrinal Kamboj, Oct 14 '16 at 17:08
@Mrinal -- That was a typo, my bad - should have been `labels` - too late to edit, whoops! — Geoff James, Oct 14 '16 at 17:13
string.Join does not seem to work as it is not recognized by Linq to Entities — navig8tr, Oct 14 '16 at 17:38

How to group by multiple columns and place contents of one field into a list of strings?

0 Answers0