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!
