EF: Database design issues regarding cross databas

Question

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:
  • EF4 cross database relationships
  • ADO.Net Entity Framework across multiple databases
  • Entity framework 4 and multiple database
  • (msdn forum with EF devs) http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/cad06147-2168-4c20-ac23-98f32987b126

---

• 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!

Answer 1

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.