Categories
c# entity-framework linq sql

How to optimize this LINQ to EF/Sql query (many to many to many relationship)?

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:

enter image description here

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)));
return grant;

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.).

Update: Using 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…