如何优化这个LINQ到EF / 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:

目前我有以下LINQ查询：

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;

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

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?

目前唯一可以想到的是创建一个用户 - >权限缓存，以便在第一次调用后完全删除该查询，但缓存总是对我来说是最后的一个手段（特别是如果你的权限变成aso，你不会想到要清除它）。

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

更新： code> Marcin建议的任何，但不是更快...

Update: Using Any as suggested by Marcin, but is not faster...

更新2：我将 IsAllowed 移动到映射表，并将查询改为仅使用一个...

Update 2: I moved the IsAllowed to the mapping table and adapted the query to use only one...

推荐答案

您应该更改每个 Count（）> 0 语句到任何方法调用，例如：

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

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

应该替换为：

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

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