如何将这个LINQ到EF / SQL查询优化（很多到多对多的关系）？ [英] How to optimize this LINQ to EF/Sql query (many to many to many relationship)?

问题描述

我实施许可制度，让使用者在角色和这个角色再有任何权限和我想到的最快的方式对它们进行查询：

目前，我有以下的LINQ查询：

  VAR补助= db.Permissions.Any（p => p.Group ==团放大器;&放，p .PermissionKey ==许可
和;&安培;！p.Roles.Any（R = GT;！r.IsAllowed和放大器;&安培; r.Role.Users.Any（U => u.UserName ==用户.Identity.Name））
和;&安培; p.Roles.Any（R = GT; r.IsAllowed和放大器;&安培; r.Role.Users.Any（U => u.UserName ==用户。 Identity.Name）））; 
 
返回授予; 
  

这需要EF有缓存的实体（15-20ms第一次）约1-2MS后。这是不是很慢，但是因为这可查询大量的（例如菜单系统检查每一个项目，如果允许用户看到该项目）的我问，如果有可能的事快？

我可以在此刻想到的唯一的事情就是创建一个用户< - >许可高速缓存中的第一个电话后都摆脱了查询，但缓存，一直是我的（尤其是当你NNED想清除它，如果权限更改ASO）的最后一招。

更新：是任何由马辛建议，但速度更快...

更新2：我搬到了 IsAllowed 来映射表和适应查询只使用一个...

解决方案

您应该改变每计数（）> 0 语句转换成任何方法调用，例如：

  R => r.Users 
 .Count之间（U => u.UserName == user.Identity.Name）GT; 
 
 
 <$ P：0 
  

应该被替换$ p> R => r.Users
。任何（U => u.UserName == user.Identity.Name）


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

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

解决方案

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)

这篇关于如何将这个LINQ到EF / SQL查询优化（很多到多对多的关系）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！