具有DbContext和TenantId的多租户-拦截器，过滤器，EF代码优先

看起来很相似，但很简单: 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);尽管这看起来像是A的愚蠢版本，但存在相同的问题.到现在为止，我认为必须有一个成熟的，可取的配置/体系结构来适应这一需求.我们应该怎么做?解决方案我想提出以下方法，1.为每个包含核心业务数据的表创建一个名称为tenant ID的列，这是任何映射表都不需要的.通过创建返回IQueryable的扩展方法来使用方法B.此方法可以是dbset的扩展，因此任何编写filter子句的人都可以调用此扩展方法，后跟谓词.这将使开发人员更轻松地完成任务，而不必担心租户ID过滤器.此特定方法将具有代码，该代码可根据正在执行此查询的租户上下文为租户ID列应用过滤条件. 样本ctx.TenantFilter().Where(....) 您可以在所有服务方法中传递承租人ID，而不是依赖于http上下文，从而可以轻松处理Web和Web Job应用程序中的承租人联系人.这样一来，通话就不再需要任何联系人，并且更易于测试.多租户实体接口方法看起来不错，并且在我们的应用程序中确实存在类似的限制，到目前为止，它仍然可以正常工作. 关于添加索引，您需要在具有租户ID的表中为租户ID列添加索引，并且应注意数据库端查询索引部分. 关于身份验证部分，我建议将asp.net身份2.0与owin管道一起使用.该系统具有高度可扩展的可定制性，并且可以在将来需要时轻松与任何外部身份提供商集成. 请确实查看实体框架的存储库模式，该模式使您可以以通用方式编写较少的代码.这将帮助我们摆脱代码重复和冗余，并且非常容易从单元测试用例中进行测试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: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 implementationPerformanceAPPROACH AThis 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 BFound here: http://howtoprogram.eu/question/n-a,28158Seems 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? 解决方案 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.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.Samplectx.TenantFilter().Where(....)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.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.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.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 这篇关于具有DbContext和TenantId的多租户-拦截器，过滤器，EF代码优先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！