具有DbContext和TenantId的多租户-拦截器，过滤器，EF代码优先 [英] MultiTenancy with DbContext and TenantId - Interceptors, Filters, EF Code-First
问题描述
我的组织需要有一个共享数据库，共享架构多租户数据库.我们将根据TenantId进行查询.我们将有很少的租户(少于10个)，并且所有租户将共享相同的数据库模式，而不支持特定于租户的更改或功能.租户元数据将存储在内存中，而不是DB(静态成员)中.
这意味着所有实体现在都需要一个TenantId，并且DbContext默认需要知道要对此进行过滤.
TenantId可能由标头值或始发域来标识，除非有更明智的方法.
我已经看到各种利用拦截器的示例，但是还没有看到TenantId实现的清晰示例.
我们需要解决的问题:
- 我们如何修改当前模式以支持此模式(我想很简单，只需添加TenantId)
- 我们如何检测租户(也很简单-基于始发请求的域或标头值-从BaseController提取)
- 我们如何将其传播到服务方法(有点棘手...我们使用DI通过构造函数进行水合作用...希望避免在所有方法签名中添加
tenantId) - 一旦拥有它，我们如何修改DbContext以对此tenantId进行过滤(不知道)
- 我们如何优化性能.我们需要什么索引，如何确保查询缓存对tenantId隔离不做任何时髦的事情，等等(不知道)
- 身份验证-使用SimpleMembership，我们如何隔离
User，并以某种方式将其与租户相关联.
我认为最大的问题是4-修改DbContext.
我喜欢本文如何利用RLS，但是我不确定如何以代码优先，dbContext的方式来处理它:
我想说的是，我要寻找的是一种方法-考虑到性能-使用DbContext有选择地查询tenantId隔离的资源，而无需使用"AND TenantId = 1"等进行呼叫.
更新-我找到了一些选择，但是我不确定每种选择的优缺点，或者不确定是否有某种更好"的方法.我对期权的评价归结为:
- 易于实施
- 性能
方法A
这似乎是昂贵的"，因为每次我们新建dbContext时，都必须重新初始化过滤器:
首先，我设置租户和界面:
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; }
}
我在任何实体上实现该接口:
public class Photo : ITenantEntity
{
public Photo()
{
DateProcessed = (DateTime) SqlDateTime.MinValue;
}
[Key]
public int PhotoId { get; set; }
[Required]
public int TenantId { get; set; }
}
然后更新我的DbContext实现:
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);
}
最后，在我对DbContext的调用中，我使用了:
using (var db = new AppContext())
{
db.SetTenantId(someValueDeterminedElsewhere);
}
我对此有一个问题，因为我在大约一百万个地方更新了我的AppContext(某些服务方法需要它，有些则不需要)，因此这会使我的代码有些膨胀.还有一些关于租户确定的问题-我是否传递HttpContext，是否强制我的控制器将TenantId传递给所有服务方法调用，如何处理我没有原始域的情况(webjob调用等).
方法B
在这里找到: http://howtoprogram.eu/question/na,28158
看起来很相似，但很简单:
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:
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?
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.
Sample
ctx.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代码优先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
