Q:

How to flatten tables using LINQ

I've this query:
var usersByBranch = (from u in _db.VRT_User
                             join urb in _db.VRT_UserRoleBranch on u.UserId equals urb.UserId
                             join r in _db.VRT_Role on urb.RoleId equals r.RoleId
                             where branches.Contains(urb.BranchId)
                             select new UserRoleBranchModel
                             {
                                 UserId = u.UserId,
                                 BranchId = urb.BranchId,
                                 RoleId = urb.RoleId,
                                 RoleName = r.RoleName
                             });

In this query, for the same userId, the roleId (1-4) and RoleName with the same BranchId are returned separately.
I'd like to flatten the rows, so that a row with the same userId contains all the RoleId and RoleName within the same BranchId.
Your help is greatly appreciated.

A:

Not sure what you mean by contains, but you can't use the same UserRoleBranchModel to hold multiple roles, so an anonymous object will do the job:
var usersByBranch = (from u in _db.VRT_User
                     join urb in _db.VRT_UserRoleBranch on u.UserId equals urb.UserId
                     join r in _db.VRT_Role on urb.RoleId equals r.RoleId
                     where branches.Contains(urb.BranchId)
                     group r by new { urb.UserId, urb.BranchId } into rg
                     select new {
                         UserId = rg.Key.UserId,
                         BranchId = rg.Key.BranchId,
                         Roles = rg.Select(r => r)
                     });

