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:
- How do we modify the current schema to support this (simple I think, just add TenantId)
- How do we detect the tenant (simple as well - base it on the originating request's domain or header value - pulling from a BaseController)
- 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) - How do we modify DbContext to filter on this tenantId once we have it (no idea)
- 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)
- 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:
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:
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?
Of course, don't replace references where you use DbSet to modify entities- so really just add and delete, right? You can still Get and then Update? For each entity I'm implementingDbSet NonTenantThing(for code-first migration, adds, deletes) andIQueryable TenantThingfor everything else. Does that sound right? – Wonacott