Entity Framework Linq Query on a Many to Many Relationship?

Question

I have the following models:

public class ApplicationUser : IdentityUser<string, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
{
    public bool HasPermission(string permission)
    {
        return Roles.Any(r => r.Role.Permissions
                  .Any(p => p.Name == permission));
    }
    // Some other Stuff

}

public class ApplicationRole : IdentityRole<string, ApplicationUserRole>
{
    public ApplicationRole(string name) : this()
    {
        this.Name = name;
    }

    public virtual ICollection<Permission> Permissions { get; set; }
 }

public class ApplicationUserRole : IdentityUserRole
{
    public ApplicationUserRole(): base() { }

    public virtual ApplicationRole Role { get; set; }
}

public class Permission
{
    public byte Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<ApplicationRole> Roles { get; set; }
}

Basically they map to five tables: Users, Roles, UserRoles, Permissions and RolePermissions - currently I don't have a model for RolePermission - Do I need one?

I want to get a list of permissions for a given user using linq with lambda expressions, something similar to the following SQL:

Select p.Id, p.Name 
From Permission p
inner join RolePermission rp on rp.PermissionId = p.Id
inner join UserRole ur on ur.RoleId = rp.RoleId 
where ur.UserId = 'xyz' 

I'm not sure which entity I start with to get a list of permissions using LINQ - I can only manage to get to the roles using the following?

var permissions =  _context.Users.Where(u => u.Id == userId)
                               .SelectMany(r => r.Roles.SelectMany(p => p.Role.Permissions))
                               .ToList();

Any ideas?

Answer 1

Managed to work it out as follows:

var permissions =  _context.Users.Where(u => u.Id == userId)
                           .SelectMany(r => r.Roles.SelectMany(p => p.Role.Permissions)).Distinct().ToList();