I have 4 tables:

USER:
-userKey
-userName

ROLE:
-roleKey
-roleDesc

PERMISSION:
-permissionKey
-permissionDesc

USERROLEPERMISSION:
-userKey
-roleKey
-permissionKey

Here is the db query…

select
u.userKey,
u.userName,
r.roleKey,
r.roleDesc,
p.permissionKey,
p.permissonDesc
FROM
USER u
JOIN USERROLEPERMISSION urp  on u.userKey = urp.userKey
JOIN ROLE r on urp.roleKey = r.roleKey
JOIN PERMISSION p on urp.permissionKey = p.permissionKey

Here is the db result…

userkey username                rolekey roledesc                permissionkey   permissiondesc
1       Captain Jack Sparrow    1       Admin                   1               Admin
1       Captain Jack Sparrow    2       Generic Data Access     5               Update
1       Captain Jack Sparrow    2       Generic Data Access     3               Read

I want to run a LINQ query to yield…

[
    {
        "userKey": 1,
        "userName": "Captain Jack Sparrow",
        "rolePermissionSet": [
            {
                "roleKey": 1,
                "roleDesc": "Admin",
                "permissionSet": [
                    {
                        "permissionKey": 1,
                        "permissionDesc": "Admin",
                    }
                ]
            },
            {
                "roleKey": 2,
                "roleDesc": "Generic Data Access",
                "permissionSet": [
                    {
                        "permissionKey": 5,
                        "permissionDesc": "Update",
                    },
                    {
                        "permissionKey": 3,
                        "permissionDesc": "Read",
                    }
                ]
            }
        ]
    }
]

I tried something like the following, but it never grouped how I wanted… would have a separate element for every user/role/permission combination. I’m thinking using group/by?? but I can’t get the syntax right. All the samples I looked at are rather elementary…

            var query =
                (from u in _dbContext.Users
                join urp in _dbContext.SAIMUserRolePermissions on u.UserKey equals urp.UserKey
                 select new UserRolePermissionSet
                 {
                     UserKey = u.UserKey,
                     UserName = u.UserName,
                     RolePermissionSet = (from rd in _dbContext.RoleDescriptions
                            where urp.UserKey == u.UserKey
                            where urp.RoleKey == rd.RoleKey
                            select new RolePermissionSet
                            {
                                RoleKey = rd.RoleKey,
                                RoleDescription = rd.RoleDescription,
                                PermissionSet = (from pd in _dbContext.PermissionDescriptions
                                              where urp.UserKey == u.UserKey
                                              where urp.RoleKey == rd.RoleKey
                                              where urp.RolePermissionKey == pd.RolePermissionKey
                                              select new RolePermissionDesc
                                              {
                                                  RolePermissionKey = pd.RolePermissionKey,
                                                  RolePermissionDescription = pd.RolePermissionDescription
                                              }).ToList()
                            }).ToList(),
                 }).ToListAsync();