I am implementing a permission system, where Users are in Roles and this roles then have permissons and I am thinking of the fastest way to query them:
At the moment I have following LINQ query:
var grant = db.Permissions.Any(p => p.Group == group && p.PermissionKey == permission
&& !p.Roles.Any(r => !r.IsAllowed && r.Role.Users.Any(u => u.UserName == user.Identity.Name))
&& p.Roles.Any(r => r.IsAllowed && r.Role.Users.Any(u => u.UserName == user.Identity.Name)));
This takes about 1-2ms after EF has the entities cached (15-20ms the first time). This is not very slow, but as this can be queried a lot (e.g. the menu system checks for every item if the user is allowed to see that item) I am asking if there is something faster possible?
The only thing I can think of at the moment is to create a User<->Permission cache to get rid of the query at all after the first call, but caching is always a last resort for me (especially as you nned to think of clearing it if permissions change aso.).
Any as suggested by Marcin, but is not faster…
Update 2: I moved the
IsAllowed to the mapping table and adapted the query to use only one…