How to add parameter/s to every query in DbContext in EF Core to secure tenants data?

Question

I am building sort of multi tenant application with shared tables using .NET Core 2.0 and EF Core.

I am also using generic repository together with Unit of Work if it matters.

I want to make it properly secured and also avoid repeating the logic, so I think if it's possible to somehow modify the DbContext which I am using to for every find operation add something like: entity => entity.tenantId == userContext.tenantId.

I also have to ensure that while creating the correct tenantId is applied and do not authorize update of other tenant property, but so far this logic is included in Service Layer - correct me if I am wrong with this approach?

The IUserContext is defined in Domain abstractions and the application layer implements it differently (API or Web App), but I am not sure if it is not code smell/anti pattern when data layer is doing this kind of logic? ( I am afraid it is).

Should this logic go to the Services (it will then have to be repeated many times which is not good idea I think), DbContext or should I adjust the repository in some way?

Take a look at this, is it essentially the same question you're asking? https://stackoverflow.com/q/40441204/84206 — AaronLS, Feb 06 '18 at 23:12

Harald Coppoolse · Accepted Answer · 2020-09-07T09:21:33.130

So what you want is that if someone would write some Linq statement like this

var result = myDbcontext.myDbSet.SomeLinq(...)

It would internally be like

var result = myDbContext.myDbSet
    .Where(entity => entity.tenantId == userContext.tenantId)
    .SomeLinq(...)

So what you should do, is that when users think that they access myDbContext.myDbSet, they actually get the subset where tenantId == userContext.tenantId

I think the neat solution would be to create a class that exposes an IQueryable for every DbSet in your DbContext and hides the actual DbContext.

Something like this:

class MyOriginalDbContext : DbContext
{
    public DbSet<Student> Students {get; set;}
    public DbSet<Teacher> Teachers {get; set;}
    public DbSet<ClassRoom> ClassRooms {get; set;}
    ...
}

public MyLimitedContext : IDisposable
{
    // to be filled in constructor
    private readonly MyOriginalDbcontext dbContext = ...
    private readonly int tenantId = ...

    IQueryable<Student> Students
    {
        get
        {
            return this.dbContext.Students
                .Where(student => student.tenantId == tenantId);
        }
    }

    IQueryable<Student> Teachers
    {
        get
        {
            return this.dbContext.Teachers
                .Where(teacher => teacher.tenantId == tenantId);
        }
    }
    ...

Users won't notice the difference:

using (var dbContext = new MyLimitedContext(...))
{
     var TeachersWithTheirStudents = dbContext.Teachers
         .Join(dbContext.Student)
         .GroupBy(teacher => teacher.Id,
            ...
}

score 0 · Answer 2 · answered Jul 12 '19 at 14:07

You can use Global Query Filters. Read more here.

Such filters are automatically applied to any LINQ queries involving those Entity Types, including Entity Types referenced indirectly, such as through the use of Include or direct navigation property references

An example:

public class Blog
{
    private string _tenantId;

    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public bool IsDeleted { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");

    // Configure entity filters
    modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
    modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}

You can even disable filter for individual LINQ query if you need:

blogs = db.Blogs
    .Include(b => b.Posts)
    .IgnoreQueryFilters()
    .ToList();

how do you inject tenant id? the dbcontext would be created even before that right? — jayasurya_j, Oct 10 '19 at 05:22

How to add parameter/s to every query in DbContext in EF Core to secure tenants data?

2 Answers2