How to configure Global Query Filters in a database first approach?

Question

I try to use the global query filters to implement multi-tenancy in an ASP.NET Core web application. At the moment, I have a separate database for each tenant and configure the context in the startup.cs like that:

services.AddDbContext<dbcontext>((service, options) =>
                options.UseSqlServer(Configuration[$"Tenant:{service.GetService<ITenantProvider>().Current}:Database"])
                    .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning)),
            contextLifetime: ServiceLifetime.Scoped, optionsLifetime: ServiceLifetime.Scoped);

This works fine. Now the customer doesn't want a separate database for each tenant anymore so I added a teanntId colum to each table and want to leverage the global query filters to implement that.

As described in the documentation, I can add the query filter in the OnModelCreating method:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("
    modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
}

But I am using the database first approach so each time I generate the model I will lose that configuration. Is there any other way to configure the global query filter like using the DbContextOptionsBuilder?

I am using EF Core 2.1.2.

Have you tried with a partial class for your DbContext to override OnModelCreating method? — H. Herzl, Oct 01 '18 at 17:10
@H.Herzl Good Idea but I would still have to keep modifying the generated code :( — Martin Brandl, Oct 01 '18 at 19:02
@MartinBrandl did you ever get anywhere with this? other than modifying the code like this https://stackoverflow.com/questions/52182040/how-to-extend-dbcontext-with-partial-class-and-partial-onmodelcreating-method-in — Simon_Weaver, Feb 13 '19 at 19:34
@Simon_Weaver I used the workaround mentioned by H. Herzl. I answered my questiion. — Martin Brandl, Feb 14 '19 at 05:26

score 2 · Accepted Answer · answered Feb 14 '19 at 05:25

I ended up using a partial class that overrides the OnModelCreating method:

public partial class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        OnModelCreatingInternal(modelBuilder);
        modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("
        modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
    }
}

I still have to modify the generated code (change the generated OnModelCreating signatur to OnModelCreatingInternal and remove the override). But at leaset I get a compiler error so I can't forget it.

In EF Core 3.1 you can use a partial method: partial void OnModelCreatingPartial(ModelBuilder modelBuilder) { } — Kellie, Nov 18 '21 at 06:07

yanbu · Answer 2 · 2020-03-19T19:22:01.337

This is the first thing that pops up on google when search this topic, so I'm posting a bit more comprehensive, easier to use solution that I came up with after knocking this around a while.

I wanted to be able to automatically filter all generated entities that had a column in the table named TenantID, and automatically insert the TenantID of the logged in user when saving.

Example partial class:

public partial class Filtered_Db_Context : MyDbContext
{
    private int _tenant;

    public Filtered_Db_Context(IHttpContextAccessor context) : base()
    {
  _tenant = AuthenticationMethods.GetTenantId(context?.HttpContext);
}

public Filtered_Db_Context(HttpContext context) : base()
{
  _tenant = AuthenticationMethods.GetTenantId(context);
}

public void AddTenantFilter<T>(ModelBuilder mb) where T : class
{
  mb.Entity<T>().HasQueryFilter(t => EF.Property<int>(t, "TenantId") == _tenant);
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  //For any entity that has a TenantId it will only allow logged in user to see data from their own Tenant
  foreach (var entityType in modelBuilder.Model.GetEntityTypes())
  {
    var prop = entityType.FindProperty("TenantId");
    if (prop != null && prop.ClrType == typeof(int))
    {
      GetType()
        .GetMethod(nameof(AddTenantFilter))
        .MakeGenericMethod(entityType.ClrType)
        .Invoke(this, new object[] { modelBuilder });
    }
  }
}

public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
  InsertTenantId();
  return base.SaveChanges(acceptAllChangesOnSuccess);
}

public override int SaveChanges()
{
  InsertTenantId();
  return base.SaveChanges();
}


public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
  InsertTenantId();
  return base.SaveChangesAsync(cancellationToken);
}

public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
{
  InsertTenantId();
  return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
}

private void InsertTenantId()
{
  if (_tenant != 0)
  {
    var insertedOrUpdated = ChangeTracker.Entries().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified).ToList();

    insertedOrUpdated.ForEach(e => {

      var prop = e.Property("TenantId");
      int propIntVal;
      bool isIntVal = int.TryParse(prop.CurrentValue.ToString(), out propIntVal);
      if (prop != null && prop.Metadata.IsForeignKey() && isIntVal && propIntVal != _tenant)
      {
        prop.CurrentValue = _tenant;
      }
    });
  }
}

  }

Now you can do all your entity framework actions like normal using the Filtered_Db_Context class and the tenant function is handled without having to think about it on both query and save.

Just add it to dependency injection in Startup instead of your EF generated context: serivces.AddDbContext<Filtered_Db_Context>()

When you re-scaffold, no need to go in and edit any of the generated classes.

How to configure Global Query Filters in a database first approach?

2 Answers2