I need to make an Application Service method that returns a list of users which have a specific Permission granted.
I'm injecting an IPermissionChecker in my app service.
When I try to call IPermissionChecker.IsGranted inside my LINQ query (before the SQL query is actually executed), I get an exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'Boolean IsGranted(Abp.Authorization.IPermissionChecker, Int64, System.String)' method, and this method cannot be translated into a store expression.
ex:
var list = _userRepository.GetAll()
.Where(x => x.IsActive && !x.IsDeleted)
.Where(x => _permissionChecker.IsGranted(x.Id, PermissionNames.CanBeAssigneeOnTask))
.ToList();
So instead, I tried to first load all users into memory, then call IPermissionChecker.IsGranted() for each users.
foreach (AppUser appUser in list)
{
if (_permissionChecker.IsGranted(appUser.Id, PermissionNames.CanBeAssigneeOnTask))
{
...
}
}
That works but each calls to IPermissionChecker.IsGranted() issues SEVEN different sql queries (I'm monitoring with SQL Profiler). So if I have 100+ users * 7 queries = 700+ queries... it is getting very slow to execute.
Is there any way to optimize this ?
5 Answer(s)
-
0
Hi,
This is a known performance issue which has a high priority: <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate/issues/466">https://github.com/aspnetboilerplate/as ... issues/466</a> I'll optimize it in a short time.
-
0
Thanks for the insight.
For now, I did optimize using this 'hacky' approach. I inject an IRepository<RolePermissionSetting, long>, and get the list of roles which have the permission. Total resulting queries = 2. ** I'm using Permissions only at role level. No user specific permissions.
// get list of roleid that have the permission var roleIds = _rolePermissionSettingRepository.GetAll() .Where(x => x.Name == PermissionNames.CanBeAssigneeOnTask) .Select(x => x.RoleId) .ToList(); var query = _userRepository.GetAll() .Where(x => x.IsActive && !x.IsDeleted) .Where(x => x.Roles.Any(y => roleIds.Contains(y.RoleId))); -
0
Thanks.
-
0
Since the Caching feature is now implemented, I did retry to call permissionChecker.IsGranted() inside a loop. There is still 5 SQL query issued for EACH iterations. So the caching does not seems to work.
I've isolated the problem in AbpMemoryCache.cs, in the method Set():
public override void Set(string key, object value, TimeSpan? slidingExpireTime = null) { //TODO: Optimize by using a default CacheItemPolicy? _memoryCache.Set( key, value, new CacheItemPolicy { SlidingExpiration = slidingExpireTime ?? DefaultSlidingExpireTime }); var test = _memoryCache.Get(key); }Here I added a Get() right after the Set(), and the value returned is ALWAYS null. In my case:
- key = a number in string ex: "1" , "2", ...
- value = an instance of UserPermissionCacheItem
- slidingExpireTime is null so it takes DefaultSlidingExpireTime which is 1 hour
I can't understand why the memoryCache.Get() returns null right after the Set().
Any ideas ?
-
0
It's very strange. MemoryCache is .NET's standard class. It seems it's not working for your case :S Can you create an issue on Github to track it. Thanks.
