0

I'm trying to find the best way for a multi-tenancy setup for an application. I would like to have a single database for the clients with a TenantId on each table with a single table with all the tenants information. Ex. Db Tenant Table(TenantId, Code, Name, etc)

I have a base controller for all my WebApi Controllers and MVC Controllers(WebApiBaseController and AppBaseController). I would like for these two controllers to filer the results based on the current user tenantId.

Should the DbContext to take care of this? I do want to have a users.Where(u => u.TenantId == CurrenctUser.TenantId) everywhere.

I also looked at this question, but i think it is a different problem hes trying to solve.

For this project I am using: ASP.NET MVC 5 WebApi 2 Entity Framework 6 AspNet.Identity

How do I filter entities based on currently logged in users using their tenantId on a single location?

UPDATE

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false)
    {
        Configuration.ProxyCreationEnabled = false;
        Configuration.LazyLoadingEnabled = false;
        Database.SetInitializer<ApplicationDbContext>(null);            
    }

    public DbSet<Tenant> Tenants { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    { 
        modelBuilder.Configurations.Add(new TenantMap());
        modelBuilder.Configurations.Add(new ApplicationUserMap());

        // Create parameterized filter for all entities that match type ITenantEntity                
        modelBuilder.Filter(Infrastructure.Constants.TetantFilterName, (ITenantEntity b) => b.TenantId, () => string.Empty);

        // By default the filter is disable to allow unauthenticated users to login
        // FIX: This filter must be Enabled at the base controller for mvc and webapi
        // basically, it will only get activated after the user logs in. See Base Controllers
        modelBuilder.DisableFilterGlobally(Infrastructure.Constants.TetantFilterName);
        base.OnModelCreating(modelBuilder);
    }

    public void EnableTenantFilter()
    {
        this.EnableFilter(Infrastructure.Constants.TetantFilterName);             
    }

    public void DisableTenantFilter()
    {
        this.DisableFilter(Infrastructure.Constants.TetantFilterName);
    }

}

 [Authorize]   
public class WebApiBaseController : ApiController
{

    ApplicationUser _currectUser;

    public  ApplicationUser CurrentUser
    {
        get
        {
            if (_currectUser == null)
            {
                AppDb.DisableTenantFilter();
                string currentUserId = User.Identity.GetUserId();
                _currectUser = AppDb.Users.FirstOrDefault(a => a.Id == currentUserId);

                AppDb.EnableTenantFilter();
            }


            return _currectUser;

        }
        set { _currectUser = value; }
    }

    public ApplicationDbContext AppDb
    {
        get;
        private set;
    }

    public WebApiBaseController(ApplicationDbContext context)
    {
        AppDb = context;

        if (User.Identity.IsAuthenticated)
        {    
            AppDb.SetFilterGlobalParameterValue(Infrastructure.Constants.TetantFilterName, CurrentUser.TenantId); 
        }            
    }

}
Community
  • 1
  • 1
Omar Olivo
  • 45
  • 1
  • 3
  • 9
  • 2
    Adding a filter via interception may be your best bet: https://lostechies.com/jimmybogard/2014/05/29/missing-ef-feature-workarounds-filters/ – Steve Greene May 28 '15 at 15:19
  • I tried that before but I had a problem when the user was not authenticated. It was not able to locate the user because I had to define an empty tenant when the filter was created. So far, this is what I have. See Update on question – Omar Olivo May 28 '15 at 20:15
  • If you don't know the who the tenant is because you don't know who the user is, how can you query data for the unknown tenant? And, above all, why? – JotaBe May 29 '15 at 09:24
  • When using https://github.com/jcachat/EntityFramework.DynamicFilters you need to define your filters. If a user has not logged in, I need to allow that user to authenticate. However, my ApplicationDbContext is filtered by an empty tenant and therefore can't find the user during login. Please see update for a working solution. I know it is not the best approach to solve this problem. – Omar Olivo May 29 '15 at 11:56
  • Multi tenancy is much easier (and safer) if each tenant has its own db schema. You can easily map tables + schemas in EF. – Gert Arnold Jun 14 '15 at 09:26
  • I am don't know much about database schema. However, having a schema for each tenant appears to me overwhelming. At the moment, I only need a schema for 4 tenants, but what happens when the number of tenants increase(maybe 100+)? Please excuse my lack of knowledge. – Omar Olivo Jun 15 '15 at 15:01

1 Answers1

1

IMHO, I send the user context ( tenant ID, user ID) to the data access and set the tenant ID as a filter column for each query, this ensures the right data access for me.

In case you opt for controller based filtering, what if you need a rest api at a later point of time. The handling of the security system in the service &/DAL is what I find the best solution.

Saravanan
  • 7,637
  • 5
  • 41
  • 72