Many to Many and Self reference Table! Get all roles a user have. Entity Framework

user_10599506 0
Viewed 193

I have a User table which stores two types of users: User or Group. A user can belong to a group, which creates a self reference table as follows.

public class User
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Type { get; set; }  //Group || user
        public Guid GroupId { get; set; }
        public User Group { get; set; }
        public List<Role> Roles { get; set; }
    }

A user may have multiple Roles and vice versa as such I have the following two tables.

 public class Role
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public List<User> Users { get; set; }

    }

    public class UserRole
    {
        public Guid Id { get; set; }
        public Guid UserId { get; set; }
        public User User { get; set; }
        public Guid RoleId { get; set; }
        public Role Role { get; set; }

    }

Now I need a query to get all roles a user has. These roles could be directly associated to a user or inherited from a group, where a group may inherit it from another group.

I am new to entity framework core and the only thing I was able to come up with was selecting the direct roles using

var userRoles = context.User.Where(u => u.Id == id).Include(x => x.Roles).Select(x => x.Roles).ToListAsync();

Im not able to address the inheritance situation. So, how can i get this result in a form of rolename and inherited_from_group_name preferably in Entity framework core. Please help.

entity-framework-core asp.net-core entity-framework sql .net
1 Answers

I would suggest a recursive solutions,but maybe it can cause performance leaks (if many self-refrence).

The idea is that you create a function that get userId and returns a list of roles, the logic of the function is that we add the roles of the user(related to userId in params) and we add the roles of the group related to this user(we use the same method, this the trick)

For recursive functions we need stop condition, in my example, I make when Group is null , you need a stop condition and making sure that is always safe otheriwse this will cause many dangerous problems

    public List<Roles> GetUserRoles(Guid userId)
    {
        var roles = new List<Roles>();
        var user= context.User.FirstOrDefault(u => u.Id == id).Include(x => x.Roles);
        roles.AddRange(user.Roles);
        if (user.Group != null)
        {
            roles.AddRange(GetUserRoles(user.GroupId));
        }
        return roles;
    }
user_82888480
Related