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

my organization needs have shared database, shared schema multitenant database. querying based on tenantid. have few tenants (less 10) , share same database schema no support tenant-specific changes or functionality. tenant metadata stored in memory, not in db (static members).

this means entities need tenantid, , dbcontext needs know filter on default.

the tenantid identified header value or originating domain, unless there's more advisable approach.

i've seen various samples leveraging interceptors haven't seen clearcut example on tenantid implementation.

---

the problems need solve:

1. how modify current schema support (simple think, add tenantid)
2. how detect tenant (simple - base on originating request's domain or header value - pulling basecontroller)
3. how propagate service methods (a little trickier... use di hydrate via constructors... want avoid peppering of method signatures tenantid)
4. how modify dbcontext filter on tenantid once have (no idea)
5. how optimize performance. indexes need, how can ensure query caching isn't doing funky tenantid isolation, etc (no idea)
6. authentication - using simplemembership, how can isolate users, somehow associating them tenant.

i think biggest question there 4 - modifying dbcontext.

---

i how article leverages rls, i'm not sure how handle in code-first, dbcontext manner:

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

i'd i'm looking way - performance in mind - selectively query tenantid-isolated resources using dbcontext without peppering calls "and tenantid = 1" etc.

---

update - found options, i'm not sure pros , cons each, or whether or not there's "better" approach altogether. evaluation of options comes down to:

• ease of implementation
• performance

---

approach a

this seems "expensive" since every time new dbcontext, have re-initialize filters:

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

first, set tenants , interface:

public static class tenant {      public static int tenanta {         { return 1; }     }     public static int tenantb     {         { return 2; }     }  }  public interface itenantentity {     int tenantid { get; set; } } 

i implement interface on entities:

 public class photo : itenantentity  {      public photo()     {         dateprocessed = (datetime) sqldatetime.minvalue;     }      [key]     public int photoid { get; set; }      [required]     public int tenantid { get; set; }  } 

and update 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 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 calls dbcontext, use this:

     using (var db = new appcontext())      {           db.settenantid(somevaluedeterminedelsewhere);      } 

i have problem because new appcontext in million places (some service methods need it, don't) - bloats code bit. there questions tenant determination - pass in httpcontext, force controllers pass tenantid service method calls, how handle cases don't have originating domain (webjob calls etc).

---

approach b

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

seems similar, 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 seems dumb version of same concerns.

i figure point in time, there has mature, advisable configuration/architecture suit need. how should go this?

i suggest following approach, 1. create column name tenant id each of table contains core business data not required mapping table.

2. use approach b, creating extension method returns iqueryable. method can extension of dbset writing filter clause, can call extension method followed predicate. make task easier developers write code without bothering tenant id filter. particular method have code apply filter condition tenant id column based on tenant context in query being executed.

sample ctx.tenantfilter().where(....)

3. instead of relying upon http context can have tenant id passed in of service methods easy handling tenant contacts in both web , web job applications. makes call free contacts , more testable. multi tenant entity interface approach looks , have similar limitation in our application works fine far.

4. regarding adding index required add index tenant id column in tables have tenant id , should take care of db side query indexing part.

5. regarding authentication part, recommend use asp.net identity 2.0 owin pipeline. system extensible customisable , easy integrate external identity providers if need in future.

6. please take @ repository pattern entity framework enables write lesser code in generic fashion. rid of code duplication , redundancy , easy test unit test cases