EF：有关跨数据库关系的数据库设计问题 [英] EF: Database design issues regarding cross database relations
问题描述
摘要
我目前正在为用户（存储在数据库 1 ）可以注册到不同的租户（存储在数据库每个租户（相同的数据库模式）中。我认为适用于许多多租户解决方案的架构。
令人遗憾的是，我发现实体框架中不支持跨数据库关系（我认为EF6仍然是这样），我提供了以下链接。
下面的小节解释了我的问题，最终解决了我的问题。
设计背后的理性
我选择有单独的数据库，一个用于用户（1），一个用于每个租户，其客户具体信息，这样一个用户在加入另一个租户时就不必创建一个新帐户（一个客户可以为不同部门提供不同的域名）。
如何实现ed
我使用两个不同的 DbContext ，其中一个用于用户，一个用于租户信息。在 TenantContext 中，我定义了 DbSet ，其中包含引用用户实体（导航属性）。
租户上下文：
public class CaseApplicationContext：DbContext，IDbContext
{
public DbSet< CaseType> CaseTypes {get;组; }
public DbSet< Case>案例{get;组; }
//省略一些不相关的代码
}
案例实体：
[Table（Cases）]
public class case：IEntity
{
public int Id {get;组; }
public User Owner {get;组; } //< ==导航属性
public string Title {get;组; }
public string描述{get;组; }
public Case（）
{
Tasks = new List< Task>（）;
}
}
用户实体
[Table（Users）]
public class User：IEntity
{
public int Id {get;组; }
public string Name {get;组; }
public string EmailAddress {get;组; }
public string Password {get;组; }
}
这个用户实体也由我的其他DbContext派生物由 Users 数据库所包含：
public class TenantApplicationContext：DbContext，IDbContext
{
public DbSet< Tenant>租客{get;组; }
public DbSet< User>用户{get;组; } //< ==这里再次
//省略无关代码
}
现在，出了什么问题？
预期：
我会（所有我的愚蠢）认为会发生是我实际创建一个跨数据库关系：
租户数据库包含一个表案例。此表包含带有UserID的行。 UserID是指用户数据库。
实际：
当我开始添加 Case s我也在我的租户数据库中创建另一个表用户。在我的'case'表中， UserID 是指同一数据库中的表。
跨数据库关系不存在于EF
所以我开始搜索，发现这个功能根本不被支持。这让我想起来，我甚至应该使用EF这样的应用程序吗？我应该转向NHibernate吗？但是，我也无法想象，微软实体框架忽略了多租户应用程序的巨大的市场！所以我最可能在做一些比较愚蠢的事情。
最后，问题...
我认为主要的问题是关于我的数据库设计。由于我是新来的EF，学习，我走了，我可能会错了转几次（我的设计是否破碎？）。由于SO与EF专家有很好的代表性，所以我非常希望了解可以使用哪些替代方案来实现同样的功能（多租户，共享用户，可在天蓝色中部署）。我应该使用一个单一的 DbContext ，并且仍然能够使用共享的 Users 数据库来部署多租户Web应用程序。
我非常感谢您的帮助！
学习：
- NHibernate确实支持跨数据库关系（但我想部署到Azure，而是坚持使用微软技术）
- 视图或Synomyms可以是一种替代方案（不确定是否会在Azure中造成更多困难）
- EF不支持跨数据库关系：
- PS：我意识到这是一个漫长的问题。随意编辑问题并删除不相关的部分以提高可读性。
- PPS：如果需要，我可以分享更多的代码
提前谢谢你。我很乐意为你所有的努力赢得升值！
我不太明白你为什么需要跨数据库关系在所有假设您的应用程序可以与两个数据库，用户数据库和租户数据库进行通信，可以轻松地使用第一个数据库进行身份验证，然后在租户数据库中以按名称约定查找相关用户。
例如，如果您使用用户数据库验证用户JOHN，则在租户数据库中搜索用户JOHN。
这将更容易实现，仍然符合您的要求，用户存储在用户数据库中以及用户记录的密码和影子副本，但没有密码存储在租户数据库中，这两者之间没有任何物理关系。
Summary
I am currently prototyping a (very straight-forward?) multi-tenant web-application where users (stored in database 1) can register to different tenants (stored in a database per tenant (same db schema). An architecture that I thought would apply to a lot of multi tenant solutions.
Sadly, I found out that cross database relations are not supported in Entity Framework (I assumed it's still the case for EF6). I provided the links below.
The next short sections explain my problem, and ultimately my question(s).
The rational behind the design
I choose to have separate databases; one for users (1), and one for each tenant with their customer specific information. That way a user does not have to create a new account when he joins another tenant (one customer can have different domains for different departments).
How it's implemented
I implemented this using two different DbContexts, one for the users, and one for the tenant information. In the TenantContext I define DbSets which holds entities which refer to the User entity (navigation properties).
The 'per-tenant' context:
public class CaseApplicationContext : DbContext, IDbContext
{
public DbSet<CaseType> CaseTypes { get; set; }
public DbSet<Case> Cases { get; set; }
// left out some irrelevant code
}
The Case entity:
[Table("Cases")]
public class Case : IEntity
{
public int Id { get; set; }
public User Owner { get; set; } // <== the navigation property
public string Title { get; set; }
public string Description { get; set; }
public Case()
{
Tasks = new List<Task>();
}
}
The User entity
[Table("Users")]
public class User : IEntity
{
public int Id { get; set; }
public string Name { get; set; }
public string EmailAddress { get; set; }
public string Password { get; set; }
}
This User entity is also contained by the Users database by my other DbContext derivative:
public class TenantApplicationContext : DbContext, IDbContext
{
public DbSet<Tenant> Tenants { get; set; }
public DbSet<User> Users { get; set; } // <== here it is again
// left out irrelevant code
}
Now, what goes wrong?
Expected:
What I (in all my stupidity) thought that would happen is that I would actually create a cross database relation:
The 'per-tenant' database contains a table 'Cases'. This table contains rows with a 'UserID'. The 'UserID' refers to the 'Users' database.
Actual:
When I start adding Cases I am also creating another table 'Users' in my 'per-tenant' database. In my 'cases' table the UserID refers to the table in the same database.
Cross database relations do not exist in EF
So I started googling, and found that this feature simply is not supported. This made me think, should I even use EF for an application like this? Should I move towards NHibernate instead?
But I also can't imagine that the huge market for multi tenant applications simply is ignored by Microsoft's Entity Framework?! So I most probably am doing something rather stupid.
Finally, the question...
I think the main question is about my 'database design'. Since I am new to EF and learning as I go, I might have taken the wrong turn on several occasions (is my design broken?). Since SO is well represented with EF experts I am very eager to learn which alternatives I could use to achieve the same thing (multi tenant, shared users, deployable in azure). Should I use one single DbContext and still be able to deploy a multi tenant web-application with a shared Users database?
I'd really appreciate your help!
Things learned:
- NHibernate does support cross database relations (but I want to deploy into Azure and rather stick to microsoft technologies)
- Views or Synomyms can be an alternative (not sure if that will cause more difficulties in Azure)
- Cross database relations are not supported by EF:
- PS: I realize this is a lengthy question. Feel free to edit the question and remove irrelevant parts to improve the readability.
- PPS: I can share more code if needed
Thank you so much in advance. I will gladly reward you with upvotes for all your efforts!
I don't quite understand why do you need cross database relations at all. Assuming your application can talk to the two databases, the user database and a tenant database, it can easily use the first database for authentication and then find related user in the tenant database with "by name" convention.
For example, if you authenticate a user JOHN using user database then you search for a user JOHN in the tenant database.
This would be much easier to implement and still match your requirements, users are stored in users database together with their passwords and "shadow copies" of user records but with no passwords are stored in tenant databases and there is NO physical relation between these two.
这篇关于EF：有关跨数据库关系的数据库设计问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
