CODEWITHSUNDEEP
c#sqllinqentity-framework
Christoph Fink
Christoph Fink

Reputation: 23093

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

Upvotes: 4

Views: 703

Answers (2)

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

You should change every Count() > 0 statement into Any method call, e.g.:

r => r.Users
      .Count(u => u.UserName == user.Identity.Name) > 0

should be replaced with:

r => r.Users
      .Any(u => u.UserName == user.Identity.Name)

Upvotes: 4

Kyllan
Kyllan

Reputation: 103

Have you tried creating an indexed view in the database instead?

CREATE VIEW mySchema.UserRolePermission
WITH SCHEMABINDING
/* your SELECT goes here */
GO
CREATE UNIQUE CLUSTERED INDEX MyIndexName
ON mySchema.UserRolePermission (UserName, PermissionKey);
GO

Then your LINQ2SQL query becomes a straight select from this view instead. It may be faster because SQL server will create an index on the UserName and PermissionKey fields that you are using to do your search. Since this is a permission system I am assuming that you are not inserting into these tables that often (the indexed view may slow inserting down a little) but that you are reading from it more often.

The second time you fire this query off may also not be faster because of EF caching, but instead may be because of SQL server caching. Not sure, but may be worth trying it out.

Upvotes: 0

Related Questions