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

.net-core asp.net-core c# entity-framework-core

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.

1
1
10/2/2018 6:48:39 AM

Accepted Answer

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.

1
2/14/2019 5:25:56 AM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow