Entity Framework for Multi-tenant architecture - filterings single table by tenant ID

Question

We are looking for a way of automatically filtering all CRUD operations by a tenant ID in Entity Framework.

The ideas we thought of were:

• Using table valued user defined functions
• Using stored procedures (but we don't really want to, as we're using an ORM to avoid doing so)
• Some how modifying the templates used to generate the SQL to add a where clause on each statement.
• Some how modifying the templates used to generate the LINQ in the controllers (we may use MVC).

Any tips?

-thanks Alex.

Answer 1

Using table valued user defined functions

Table valued function are only available in .NET 4.5 Beta (and not available in code first). Using them will still not help you because you will have to use the function in every LINQ query so it is the same as using where clause.

Using stored procedures (but we don't really want to, as we're using an ORM to avoid doing so)

It can be useful for some special complex queries but generally it is not what you want.

Some how modifying the templates used to generate the SQL to add a where clause on each statement.

Too complex and on completely different level of abstraction.

Some how modifying the templates used to generate the LINQ in the controllers (we may use MVC).

Close to ideal solution. You simply need to wrap access to your entity set into some code which will look like:

public class MultiTenantAccess<T> where T : IMultitenant
{
    private IDbSet<T> set;  

    ... 

    public IQueryable<T> GetQuery(int tenantID) 
    {
        return set.Where(e => e.TenantID == tenantID); 
    }
}

Sometimes this is core for something called Generic repository but it is really just a wrapper around EF set. You will always use GetQuery to query your data store instead of using DbSet directly.

Answer 2

you may also separate the tenants data into different databases
or into same database, but with different schemas? You can read more about this in an old MSDN article called "Multi-Tenant Data Architecture"