LINQ subset results grouped from multi-table join

Question

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();

Here are the models and dbcontext particulars...

    public DbSet<UserRolePermission> SAIMUserRolePermissions { get; set; }
    public DbSet<RoleDesc> RoleDescriptions { get; set; }
    public DbSet<RolePermissionDesc> PermissionDescriptions { get; set; }
    public DbSet<UserRolePermissionSet> UserRoles { get; set; }
    public DbSet<Participant> Participants { get; set; }
    public DbSet<User> Users { get; set; }

    modelBuilder.Entity<UserRolePermission>().ToTable("userrolepermission");
    modelBuilder.Entity<RoleDesc>().ToTable("role");
    modelBuilder.Entity<RolePermissionDesc>().ToTable("permission");
    modelBuilder.Entity<User>().ToTable("user");

    [Table("userrolepermission")]
    public class UserRolePermission
    {
        [Key()]
        [Column("userrolekey")]
        public long UserRoleKey
        {
            get; set;
        }

        [Required()]
        [Column("userkey")]
        public long UserKey
        {
            get; set;
        }

        [Required()]
        [Column("rolekey")]
        public long RoleKey
        {
            get; set;
        }

        [Required()]
        [Column("rolepermissionkey")]
        public long RolePermissionKey
        {
            get; set;
        }
    }
    
    [Table("role")]
    public class RoleDesc
    {
        [Key()]
        [Column("rolekey")]
        public long RoleKey
        {
            get; set;
        }

        [Required()]
        [Column("roledesc")]
        public string? RoleDescription
        {
            get; set;
        }
    }
    
    [Table("permission")]
    public class RolePermissionDesc
    {
        [Key()]
        [Column("rolepermissionkey")]
        public long RolePermissionKey
        {
            get; set;
        }

        [Required()]
        [Column("rolepermissondesc")]
        public string? RolePermissionDescription
        {
            get; set;
        }
    }
    
    public class UserRolePermissionSet
    {
        [Key()]
        public long UserKey
        {
            get; set;
        }

        public string? UserName
        {
            get; set;
        }

        public List<RolePermissionSet>? RolePermissionSet
        {
            get; set;
        }
    }

    [Table("user")]
    public class User
    {
        [Key()]
        [Column("userkey")]
        public long UserKey
        {
            get; set;
        }

        [Column("username")]
        public string? UserName
        {
            get; set;
        }
    }

Answer 1

Well, looks like you do not have appropriare navigation properties and join is needed.

// request only what you need
var query =
    from u in _dbContext.Users
    join urp in _dbContext.SAIMUserRolePermissions on u.UserKey equals urp.UserKey
    join r in _dbContext.RoleDescriptions on urp.roleKey equals r.roleKey
    join p in _dbContext.PermissionDescriptions on urp.permissionKey equals p.permissionKey
    select new
    {
        u.UserKey,
        u.UserName,
        urp.RoleKey,
        r.RoleDescription,
        p.PermissionKey,
        p.PermissionDesc
    };

// materialize the query
var flattentResult = await query.ToListAsync(); 

// perform grouping to achieve desired result on the client side
var result = flattentResult
    .GroupBy(ug => new { ug.UserKey, ug.UserName })
    .Select(g => new UserRolePermissionSet
    {
        userKey = g.Key.UserKey,
        userName = g.Key.UserName,
        rolePermissionSet = g
            .GroupBy(rg => new { rg.RoleKey, rg.RoleDescription })
            .Select(rg => new RolePermissionSet
            {
                RoleKey = rg.Key.RoleKey,
                RoleDescription = rg.Key.RoleDescription,
                PermissionSet = rg.Select(p => new RolePermissionDesc
                {
                    RolePermissionKey = p.PermissionKey,
                    RolePermissionDescription = p.PermissionDesc
                }).ToList()
            }).ToList()
    })
    .ToList();