HanybymnGracy
New Member
I have a roles table in my SQL Server 2008 database with different roles for content users of a website (e.g. Admin, Director, Manager, ..., Basic User). Using LINQ to Entities I need to retrieve query results for different roles depending on how high up the hierarchy of permissions they are.Roles are not in a linear hierarchy, the Admin role is the root with the highest permissions, but there are roles such as Manager and Director which are not above or below each other but have different read/modify permissions for the same data. The data is visible to both, but some data that is read only for Directors is modifiable by Managers, and vice versa. New roles can be added that are a sub-role of Director or Manager and have access to only a subset of their respective data sets and more restrictive read/modify permissions. Basic Users are the least role and have the most restricted data access, but are also a sub-role of both Director and Manager (thus this is a DAG and not a tree).I'm trying to design a role hierarchy in the database (with preferably only one new table) that allows different branches of this DAG relationship to be easily query-able using LINQ to Entities' navigation properties. So something along the lines of:\[code\]var RoleRestrictedEntities = Entities.Where(e => e.User.Role.RoleType <= currentUserRole.RoleType);\[/code\]with a suitable replacement for the \[code\]<=\[/code\] operator if necessary (I know I can't do operator overloading in LINQ to Entities). Note that it's fine for RoleType to actually be a table with it's own fields (e.g. \[code\]int Role.RoleType.Level\[/code\]) and not just an integer field of Role.The basic purpose of this role hierarchy is to distinguish between who can modify data, who can view data as read only, and who can't even see the data at all. Higher level roles can see or possibly modify everything that users with roles beneath them worked on. They cannot see data that roles above them worked on.This needs to be fairly fast to implement (not many tables), not too difficult to insert new roles into the hierarchy or delete old ones (these operations will be infrequent, but I don't want a debugging nightmare), and above all easy to query for a role and its sub-roles using LINQ to Entities. Any suggestions on how I can tackle this?