MultiTenancy with DbContext and TenantId - Interceptors, Filters, EF Code-First

Question

My organization needs to have a shared database, shared schema multitenant database. We will be querying based on TenantId. We will have very few tenants (less than 10) and all will share the same database schema with no support for tenant-specific changes or functionality. Tenant metadata will be stored in memory, not in the DB (static members).

This means all entities will now need a TenantId, and DbContext needs to know to filter on this by default.

The TenantId will likely be identified by a header value or the originating domain, unless there's a more advisable approach.

I've seen various samples leveraging interceptors for this but haven't seen a clearcut example on a TenantId implementation.

---

The problems we need to solve:

1. How do we modify the current schema to support this (simple I think, just add TenantId)
2. How do we detect the tenant (simple as well - base it on the originating request's domain or header value - pulling from a BaseController)
3. How do we propagate this to service methods (a little trickier... we use DI to hydrate via constructors... want to avoid peppering all of the method signatures with tenantId)
4. How do we modify DbContext to filter on this tenantId once we have it (no idea)
5. How do we optimize for performance. What indexes do we need, how can we ensure that query caching isn't doing anything funky with the tenantId isolation, etc (no idea)
6. Authentication - using SimpleMembership, how can we isolate Users, somehow associating them with a tenant.

I think the biggest question there is 4 - modifying DbContext.

---

I like how this article leverages RLS, but I'm not sure how to handle this in a code-first, dbContext manner:

https://azure.microsoft.com/en-us/documentation/articles/web-sites-dotnet-entity-framework-row-level-security/

I'd say what I'm looking for is a way to - with performance in mind - selectively query tenantId-isolated resources using DbContext without peppering my calls with "AND TenantId = 1" etc.

---

Update - I found some options, but I'm not sure what the pros and cons are for each, or whether or not there's some "better" approach altogether. My evaluation of options comes down to:

• Ease of implementation
• Performance

---

APPROACH A

This seems "expensive" since every time we new up a dbContext, we have to re-initialize filters:

https://blogs.msdn.microsoft.com/mvpawardprogram/2016/02/09/row-level-security-in-entityframework-6-ef6/

First, I set up my tenants and interface:

public static class Tenant {

    public static int TenantA {
        get { return 1; }
    }
    public static int TenantB
    {
        get { return 2; }
    }

}

public interface ITenantEntity {
    int TenantId { get; set; }
}

I implement that interface on any entities:

 public class Photo : ITenantEntity
 {

    public Photo()
    {
        DateProcessed = (DateTime) SqlDateTime.MinValue;
    }

    [Key]
    public int PhotoId { get; set; }

    [Required]
    public int TenantId { get; set; }
 }

And then I update my DbContext implementation:

  public AppContext(): base("name=ProductionConnection")
    {
        Init();
    }

  protected internal virtual void Init()
    {
        this.InitializeDynamicFilters();
    }

    int? _currentTenantId = null;

    public void SetTenantId(int? tenantId)
    {
        _currentTenantId = tenantId;
        this.SetFilterScopedParameterValue("TenantEntity", "tenantId", _currentTenantId);
        this.SetFilterGlobalParameterValue("TenantEntity", "tenantId", _currentTenantId);
        var test = this.GetFilterParameterValue("TenantEntity", "tenantId");
    }

    public override int SaveChanges()
    {
        var createdEntries = GetCreatedEntries().ToList();
        if (createdEntries.Any())
        {
            foreach (var createdEntry in createdEntries)
            {
                var isTenantEntity = createdEntry.Entity as ITenantEntity;
                if (isTenantEntity != null && _currentTenantId != null)
                {
                    isTenantEntity.TenantId = _currentTenantId.Value;
                }
                else
                {
                    throw new InvalidOperationException("Tenant Id Not Specified");
                }
            }

        }
    }

    private IEnumerable<DbEntityEntry> GetCreatedEntries()
    {
        var createdEntries = ChangeTracker.Entries().Where(V => EntityState.Added.HasFlag(V.State));
        return createdEntries;
    }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Filter("TenantEntity", (ITenantEntity tenantEntity, int? tenantId) => tenantEntity.TenantId == tenantId.Value, () => null);

        base.OnModelCreating(modelBuilder);
    }

Finally, in my calls to DbContext, I use this:

     using (var db = new AppContext())
     {
          db.SetTenantId(someValueDeterminedElsewhere);
     }

I have a problem with this because I new up my AppContext in about a million places (some service methods need it, some don't) - so this bloats my code a bit. There are also questions about tenant determination - do I pass in the HttpContext, do I force my controllers to pass the TenantId into all service method calls, how do I handle cases where I don't have an originating domain (webjob calls etc).

---

APPROACH B

Found here: http://howtoprogram.eu/question/n-a,28158

Seems similar, but simple:

 public interface IMultiTenantEntity {
      int TenantID { get; set; }
 }

 public partial class YourEntity : IMultiTenantEntity {}

 public partial class YourContext : DbContext
 {
 private int _tenantId;
 public override int SaveChanges() {
    var addedEntities = this.ChangeTracker.Entries().Where(c => c.State == EntityState.Added)
        .Select(c => c.Entity).OfType<IMultiTenantEntity>();

    foreach (var entity in addedEntities) {
        entity.TenantID = _tenantId;
    }
    return base.SaveChanges();
}

public IQueryable<Code> TenantCodes => this.Codes.Where(c => c.TenantID == _tenantId);
}

public IQueryable<YourEntity> TenantYourEntities => this.YourEntities.Where(c => c.TenantID == _tenantId);

Although this just seems like a dumb version of A with the same concerns.

I figure by this point in time, there has to be a mature, advisable configuration/architecture to suit this need. How should we go about this?

Answer 1

I would like to suggest the following approach, 1. Create a column with the name tenant ID for each of the table that contains core business data this is not required for any mapping table.

2. Use the approach B, by creating an extension method that returns an IQueryable. This method can be an extension of the dbset so that anyone writing a filter clause, can just call this extension method followed by the predicate. This would make the task easier for developers to write code without bothering about tenant ID filter. This particular method will have the code to apply the filter condition for the tenant ID column based on the tenant context in which this query is being executed.

Sample ctx.TenantFilter().Where(....)

3. Instead of relying upon the http context you can have tenant ID passed in all of your service methods so that it will be easy for handling the tenant contacts in both the web and the web job applications. This makes a call free from contacts and more easily testable. The multi tenant entity interface approach looks good and we do have a similar limitation in our application which works fine so far.

4. Regarding adding index you would be required to add an index for tenant ID column in the tables that have tenant ID and that should take care of the DB side query indexing part.

5. Regarding the authentication part, I would recommend to use asp.net identity 2.0 with the owin pipeline. The system is very extensible customisable and easy to integrate with any external identity providers if need be in future.

6. Please do take a look at the repository pattern for entity framework which enables you to write lesser code in a generic fashion. This would help us get rid of code duplication and redundancy and very easy to test from unit test cases

Answer 2

I think the biggest question there is 4 - modifying DbContext.

Don't modify the Context...

You shouldn't have to mix tenant-filtering code with your business code.

I think all you need is a repository the returns filtered data
This repository will return filtered data based on an Id you'll get from a TenantIdProvider.
Then, your Service doesn't have to know anything about tenants

using System;
using System.Data.Entity;
using System.Linq;

namespace SqlServerDatabaseBackup
{
    public class Table
    {
        public int TenantId { get; set; }
        public int TableId { get; set; }
    }

    public interface ITentantIdProvider
    {
        int TenantId();
    }

    public class TenantRepository : ITenantRepositoty
    {
        private int tenantId;
        private ITentantIdProvider _tentantIdProvider;
        private TenantContext context = new TenantContext(); //You can abstract this if you want
        private DbSet<Table> filteredTables;

        public IQueryable<Table> Tables
        {
            get
            {
                return filteredTables.Where(t => t.TenantId == tenantId);
            }
        }

        public TenantRepository(ITentantIdProvider tentantIdProvider)
        {
            _tentantIdProvider = tentantIdProvider;
            tenantId = _tentantIdProvider.TenantId();
            filteredTables = context.Tables;
        }

        public Table Find(int id)
        {
            return filteredTables.Find(id);
        }
    }

    public interface ITenantRepositoty
    {
        IQueryable<Table> Tables { get; }
        Table Find(int id);
    }

    public class TenantContext : DbContext
    {
        public DbSet<Table> Tables { get; set; }
    }

    public interface IService
    {
        void DoWork();
    }

    public class Service : IService
    {
        private ITenantRepositoty _tenantRepositoty;

        public Service(ITenantRepositoty tenantRepositoty)
        {
            _tenantRepositoty = tenantRepositoty;
        }

        public void DoWork()
        {
            _tenantRepositoty.Tables.ToList();//These are filtered records
        }
    }  
}