I need to dynamically filter data from the particular table depending on user's permissions. E.g. "normal" user can see only records assigned to him but admin can see all. I'm using ninject to create DB context per request and create context by passing additional user info to a constructor. Then I apply dynamic filtering (EF6) from EntityFramework-Plus extensions:
public MyDbContext(bool isAdmin, string userId) : this()
{
if (!isAdmin)
{
this.Filter<MyTable>(table => table.Where(...));
}
}
This solution works as expected i.e. calling methods like:
ctx.MyTable.Where(...)
Results in extra join declared in filter.
But behaves oddly when I'm using method Find(). I'm using SqlServer profiler to see what happens under the hood:
- I Create context as restricted (non-admin user) - calling Find() will result in extra WHERE statement corresponding to filter's lambda expression
- Then I create the context as admin (separate request) - calling Find() will result in the same SQL expression (i expect no extra SQL clauses).
AFAIK this has something to do with query caching since adding extra line to constructor seems to solve the problem:
public MyDbContext(bool isAdmin, string userId) : this()
{
// this "solves" the problem
QueryFilterManager.ClearQueryCache(this);
if (!isAdmin)
{
this.Filter<MyTable>(table => table.Where(...));
}
}
That looks like a big overkill and it doesn't bring me any closer to understanding the problem. So here are my questions:
- Why this problem does not affect Where() but affects Find()?
- Is there any cleaner way to solve this issue? I've read about Dynamic Filters library but it's no good for me as it works only in code first model (DB first here).
- Are there better concepts of filtering data basing on per-request data (like userId in my example)?
UPDATE
This is what my lambda expression looks like:
private Func<IQueryable<MyTable>, IQueryable<MyTable>> GetFilter(string userId)
{
return t => t
.Where(c.DataScopes.Any(
x => x.AspNetGroups.Any(
ang => ang.AspNetUsers.Any(
anu => anu.Id == userId))));
}
AspNetGroups is my custom table to group users. Data persmissions are assigned to users group.
