Multi-tenant Entity Architecture - Filtering a Single Table by Tenant ID

Question

We are looking for a way to automatically filter all CRUD operations by tenant ID in the Entity Framework.

The ideas we were thinking about were:

• Using functions defined by table functions
• Using stored procedures (but we really don’t want to, because we use ORM to not do this)
• Some ways to modify the patterns used to create SQL to add a where clause in each expression.
• Some ways to change the patterns used to create LINQ in controllers (we can use MVC).

Any tips?

-Thanks, Alex.

Answer 1

Using user-defined table defined functions

The table evaluation feature is only available in .NET 4.5 Beta (and not available in the first code). Using them still will not help you, because you have to use this function in every LINQ query, so this is the same as using the where clause.

Using stored procedures (but we really don’t want to, because we use ORM to not do this)

This may be useful for some special complex queries, but usually this is not what you want.

Some ways to modify the patterns used to generate SQL to add a where clause for each statement.

Too complex and completely excellent level of abstraction.

Some ways to change the patterns used to generate LINQ in controllers (we can use MVC).

Close to the perfect solution. You just need to wrap the access to your object in some code, which will look like this:

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 the core for something called a Generic repository, but it's really just a wrapper around EF. You will always use GetQuery to query your data store instead of using DbSet .

Answer 2

you can also split tenant data into different databases
or in one database, but with different schemes? You can read more about this in an old MSDN article entitled " Multi-Tenant Data Architecture"