Base solution for your next web application
Open Closed

How to split Tenant and User Data #11281


0
4Matrix created

We want our Host database to store all our lookup tables as well as all user details, roles, permission, invoices, subscriptions etc but we also want each tenant to have their own database, which will use its own set of tables that are not specific to Asp Net Zero, e.g. Students, Classes etc. I think the best way to acheive this is to use multiple db context but I am not sure what the best aproach to use is? Has anyone else done anything similar to this?

We also have Two different types of Tenant, denoted by a field in abpTenants called "TenantType". Each tenant type will need different tables so I am thinking I need 3 db context, 1 for the Main ABP stuff and another one for each tenant type, I then need to be able to access 2 different dbContexts per tenant, The Main one and the second tenant specific one.

The reason for splitting off the user data is so that users do not need to specify a Tenant when logging in, they will have a unique email address that they log in with, which then tells us which tenant they are. I think i need something like this but I am not sure I understand it clearly?

https://stackoverflow.com/questions/49243891/how-to-use-multiples-databases-in-abp-core-zero


21 Answer(s)
  • 0
    ismcagdas created
    Support Team

    Hi,

    We have a multiple DbContext usage demo, see https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/MultipleDbContextEfCoreDemo

    You can also use [MultiTenancySide(MultiTenancySides.Tenant)] attribute on the TenantDbContext which can be autimatically selected for the tenants.

  • 0
    4Matrix created

    Thanks, I had seen that example but it works with two static connection strings but our case is quite complex.

    We effectively have THREE types of Tenant: Host, Company and Alliance. Host is pretty self explanitory, company is a “Tenant” in the traditional sense of the ABP framework whereas an Alliance is basically a “Group” of companies. An “Alliance” user can login and view summary data from all of the “Companies” that it is linked with.

    All user data for ALL Companies and Alliances should be stored in the main AspNetZero database, allowing us to have a single login page and no need to select a tenant when logging in. Users are unique and identified by their email address. It should be possible for a User to be associated with multiple Tenants. They may belong to a single tenant, e.g. using the TenantId in the users table, but another tenant should be able to give that same user access to their data. If a user is associated with multiple tenants, they will be given a choice of tenants to view after login, with the ability to Switch tenant later.

    This all means that I think we need at least 2 dbContext that can be used simultaniously:

    AspNetZeroContext This SINGLE database will contain all the standard ABP tables and store users, roles & permissions etc for all tenants. It will also have some custom tables for “Lookups” (Common data that all tenants will need READ ONLY access to) and CRM functionality such as Support Tickets and Invoices etc.

    CompanyContext Each Tenant of type “Company” will have its own database that will contain ONLY custom entities, created using ASP.NET Zero Power Tools in visuall studio.e.g. Staff Entity. The Staff entity may have an “EthnicityID” field, this ID will be taken from a “Lookup” table stored in the Main AspNetZero database.

    AllianceContext? This type of tenant may not need to be an actual dbContext as it wont have its own database, it will just need to be able to pull data from multiple "Company" databases.

    We have written the code to get the second dbContext but I am unsure of the best aproach when we do not have a SINGLE connection string for the second dbContext, it will be tenant specific? Am I missing something here?

  • 0
    ismcagdas created
    Support Team

    Hi,

    If you want to change the logic of connection string resolving for tenants, you can create a similar class to https://github.com/aspnetboilerplate/aspnetboilerplate/blob/dev/src/Abp.ZeroCore.EntityFramework/Zero/EntityFramework/DbPerTenantConnectionStringResolver.cs and use it in your app. Does that work for your case ?

  • 0
    4Matrix created

    Thanks ismcagdas, I think that is exactly what I need. So I have followed this guide https://stackoverflow.com/questions/49243891/how-to-use-multiples-databases-in-abp-core-zero which sets up the project like the MultipleDbContextEfCoreDemo. Instead of creating the MyConnectionStringResolver I need have created a DbPerTenantConnectionStringResolver as per your example. Or do I need both? I can see how MyConnectionStringResolver reads the second connection string from the appsettings.json file but I am not sure how to use DbPerTenantConnectionStringResolver? I am assuming that like the MyConnectionStringResolver, I set it up like this In the CoreModule PreInitialize void?

    Configuration.ReplaceService<IDbPerTenantConnectionStringResolver, DbPerTenantConnectionStringResolver>();
    

    When I create a new tenant, I only have the choice to tell it to use the host database or supply a connection string? I want it to use the host database for everything EXCEPT the stuff on the Second DbContext I have created. When creating a tenant, it should create a new database for that tenant using the TenantDbContext I have created and set the connectionstring property for that tenant but I am not sure how to achieve this?

  • 0
    ismcagdas created
    Support Team

    Hi,

    If I understand correctly, then you don't need a custom DbPerTenantConnectionStringResolver implementation. For the two DbContexes, you can use [MultiTenancySide(MultiTenancySides.Host)] attribute for the one of your DbContexes and [MultiTenancySide(MultiTenancySides.Tenant)] on the second one. Then, you can provide a custom connection string when creating a tenant. Could you try if that works for you ?

  • 0
    4Matrix created

    Ok so I have created a new aspnetzero solution and made the following changes:

    Added a new custom db context and added a "Person" entity to it manually

    namespace Cloud.EntityFrameworkCore
    {
        [MultiTenancySide(MultiTenancySides.Tenant)]
        public class CloudDbContextSecond : AbpDbContext
        {
            /* Define an IDbSet for each entity of the application */
            public virtual DbSet<People> People { get; set; }
    
            public CloudDbContextSecond(DbContextOptions<CloudDbContextSecond> options) : base(options)
            {
    
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder) { }
        }
    }
    

    Update the main db context to be Host:

    namespace Cloud.EntityFrameworkCore
    {
        [MultiTenancySide(MultiTenancySides.Host)]
        public class CloudDbContext : AbpZeroDbContext<Tenant, Role, User, CloudDbContext>, IAbpPersistedGrantDbContext
        {
    

    Added a custom connection string resolver

    namespace Cloud.EntityFrameworkCore
    {
        public class MyConnectionStringResolver : DefaultConnectionStringResolver
        {
            private readonly IConfigurationRoot _appConfiguration;
    
            public MyConnectionStringResolver(IAbpStartupConfiguration configuration, IHostingEnvironment hostingEnvironment)
                : base(configuration)
            {
                _appConfiguration =
                    AppConfigurations.Get(hostingEnvironment.ContentRootPath, hostingEnvironment.EnvironmentName);
            }
    
            public override string GetNameOrConnectionString(ConnectionStringResolveArgs args)
            {
                if (args["DbContextConcreteType"] as Type == typeof(CloudDbContextSecond))
                {
                    return "?";
                }
                
                return base.GetNameOrConnectionString(args);
            }
        }
    
    }
    

    Updated CloudEntityFrameworkCoreModule to add the second db context and custom connection string resolver

     public override void PreInitialize()
            {
                if (!SkipDbContextRegistration)
                {
                    Configuration.ReplaceService<IConnectionStringResolver, MyConnectionStringResolver>();
    
                    Configuration.Modules.AbpEfCore().AddDbContext<CloudDbContext>(options =>
                    {
                        if (options.ExistingConnection != null)
                        {
                            CloudDbContextConfigurer.Configure(options.DbContextOptions, options.ExistingConnection);
                        }
                        else
                        {
                            CloudDbContextConfigurer.Configure(options.DbContextOptions, options.ConnectionString);
                        }
                    });
    
                    Configuration.Modules.AbpEfCore().AddDbContext<CloudDbContextSecond>(options =>
                    {
                        if (options.ExistingConnection != null)
                        {
                            CloudDbContextSecondConfigurer.Configure(options.DbContextOptions, options.ExistingConnection);
                        }
                        else
                        {
                            CloudDbContextSecondConfigurer.Configure(options.DbContextOptions, options.ConnectionString);
                        }
                    });
    
                }
    

    When a new tenant is being created, I suply a Connection String and it creates a database with the Main DB Context but creates the users etc in the Host Database, so I am sort of half way there! How do I tell it to create the db with the Second db context for new tenants? I also need to test CRUD on the Second DB context but I cant get the Entity Generator to create entities on the second db context? I have tried changing the config file but when it runs, it just hangs? Also, I am not sure it is actually going to get a connection string to the second db context anyway. Sorry for all the questions, just struggling to get my head around all this!

  • 0
    JeffMH created

    So, I don't have a ton of time to or this might be a bit more detailed. But if this doesn't make sense just reply and I will try and fill in the gaps for you.

    Basically you need to implement seperated DbMigrator classes in the EFCore project. Here is the one that comes with the framework....

    public class AbpZeroDbMigrator : AbpZeroDbMigrator<AbpZeroTemplateDbContext>

    Basically, we have seperate Tenant databases in our system and in order to migrate the tenant that has a seperate context, create as many of these classes as you have DB Contexts.

    For Ours:

    public class AbpZeroTenantDbMigrator : AbpZeroDbMigrator<TenantDbContext>, ITenantDbMigrator

    The code for these is blank, just need a class to type the appopriate context class. I Use the interface ITenantDbMigrator to seperately identify the migrators and it helps when you inject them into managers. Then, in the TenantManager, on the create tenant, I modify the code in there to migrate using the ITenantDbMigrator and not the default one.

    In Short, I created a seperate Factory, Configurer, Context, and Migrator for each of my different context classes. Also modified the Migrator console application to use the appropriate migrator when migrating hosts / tenants.

    Hopefully that points you in the right direction.

  • 0
    4Matrix created

    Thanks Jeffmh, I have duplicated the AbpZeroDbMigrator class and renamed it and put the correct db context on it. I also already created a Factory and Configurer for my new dbContext so I think I have all the right bits now, I am just not sure ow to modify the Tenant Manager to create the correct db context? I can see where the db is created:

       //Create tenant database
                    _abpZeroDbMigrator.CreateOrMigrateForTenant(tenant);
    

    Do I just need to Add my custom Migrator to the constructor and it will automatically get injected? Appologies for my naievaty, I am a winforms developer tasked with this huge project!

  • 0
    JeffMH created

    Yea, that's what the interface was helping with. Register the Interface with the container in the Initialize method of the Module in the EFCore project.

    Configuration.ReplaceService<ITenantDbMigrator, AbpZeroTenantDbMigrator>(DependencyLifeStyle.Transient);

    Then yes, inject that into the TenantManager and you can call your migrator(s) from there. In my TenantManager, I just changed the definition of the variable already defined in the class:

    private readonly ITenantDbMigrator _abpZeroDbMigrator;

    Then the code itself in the create didn't change.

    Winform programming isn't super simple either. It takes a bit to learn the ins / outs of how the framework tackles certain things and then gets easier. These kind of things are a one time setup, then you are off to the races. It's all 1's and 0's right :)

  • 0
    4Matrix created

    Thanks Jeffmh, I am still not sure I fully understand but I relly apreciate your help! I am not sure if this is allowed on here but if you were interested in some paid work to set this up for me I would be more than happy to pay! I am quite short on time so as much as I am determind to learn, it may be best for me to just get an expert in and then I can go over the code?

  • 0
    JeffMH created

    I kind of figured maybe you hadn't put two and two together but we've talked before or I talked to someone at 4Matrix. :) You can always let me know if you need help but I also don't mind answering questions on here. I love answering questions when I know the answer I just don't get a ton of time for it all the time.

  • 0
    4Matrix created

    I have had another go and done some more googling but i still cant get it to work! If you would be interested in building a solution for me for a fee please do let me know how I can contact you directly! It would be a big help!

  • 0
    4Matrix created

    Ok so I think I have pretty much got the multiple db context thing figured out now so thank you for all your help! It was mostly the connectionstringresolver I needed. Here is a simpified version of it, just incase it helps anyone else:

    namespace MyProject.EntityFrameworkCore
    {
        public class MyConnectionStringResolver : DefaultConnectionStringResolver
        {
            private readonly IConfigurationRoot _appConfiguration;
            private readonly ICurrentUnitOfWorkProvider _currentUnitOfWorkProvider;
            private readonly ITenantCache _tenantCache;
    
            public MyConnectionStringResolver(
                IAbpStartupConfiguration configuration,
                ICurrentUnitOfWorkProvider currentUnitOfWorkProvider,
                ITenantCache tenantCache)
                : base(configuration)
            {
                _currentUnitOfWorkProvider = currentUnitOfWorkProvider;
                _tenantCache = tenantCache;
            }
    
            public override string GetNameOrConnectionString(ConnectionStringResolveArgs args)
            {
                if (args["DbContextConcreteType"] as Type == typeof(MyProjectDbContextSecond))
                {
                                    var tenantCacheItem = _tenantCache.Get(_currentUnitOfWorkProvider.Current.GetTenantId().Value);
                    return tenantCacheItem.ConnectionString;
                }
                
                return base.GetNameOrConnectionString(args);
            }
        }
    
    }
    

    So now all my main aspnetzero data is accessed from the Host Db and My custom tenant specific entities are stored in their own databases. Is this the best way to achieve it or is there a better way to identify which Connection string to use without a Type comparison? I have tried marking my second dbcontext with

    [MultiTenancySide(MultiTenancySides.Tenant)]
    

    But args.MultiTenancySide is always Host or Null? I was thinking maybe I could have some sort of dbcontext interface with a property on it I could check to see what dbcontext to use?

    Anyway, my next issue is that I am being asked to allow users to have access to multiple tenants and be able to set roles against users and tenants. My users all sign up with a unique email address and I want them to only exist once. I cant use the linked account or "Delegation" as this requires a user to map to from the tenant linked to. I could add a table for this but I am not sure it will work as even if they can login, they wont get the right data because of the tenant filters? What is the best aproach here?

  • 0
    4Matrix created

    So my current thinking is that maybe I move the authentication fields from abpUsers to abpUserAccounts. Log the user in with the info in abpUserAccounts. Each "UserAccount" will have a row in the abpUsers table for each tenant they are linked with. After logging in, they will select the Tenant they want from the ones they are linked with, which will effectivly log them in using the abpUser row? Does this make sense or is there a better approach?

  • 0
    ismcagdas created
    Support Team

    Hi Guys,

    @jeffmh, thanks a lot for your help as always :).

    @4Matrix

    allow users to have access to multiple tenants

    Could you share how will you know which user can access to which tenants ?

  • 0
    4Matrix created

    I was thinking that If I added a row to the abpUsers table for each tenant that the user is linked to but use the abpUserAccounts table to do the actaual login... So Username/Email would be unique in abpUserAccounts but will exist multiple times in abpUsers if the user is linked to multiple teants? Then when the abpUserAccount has been logged in, they will then choose an abpUser to use for the session and provide a way of switching this in the user menu, like you do for Linked Accounts? We work with Groups of schools and certain users will need access to all the schools in their groiup.

    Thank you @jeffmh and @ismcagdas for all your help, I love asp net zero and getting support through the forum is great!

  • 0
    ismcagdas created
    Support Team

    Hi @4Matrix

    Thank you for the detailed explanation. Your approach seems fine for your case. I couldn't estimate any problem but if you face a problem, please let us know.

  • 0
    4Matrix created

    Sorry to keep coming back to this but I still don't have a working solution for this. We have 2 different types of tenant, which is denoted by a property on the Tenant called "TenantType". I have created 2 extra dbContexts for these, each with their own DbContextConfigurer, DbContextFactory and DbMigrator Class (Which inherits from my iTenantMigrator interface, as suggested above). I have also created a CustomConnectionStringResolver class but I dont really want to use args["DbContextConcreteType"] in order to work out what dbContext is requesting the connection string, is there not a better way? I was hoping to use args.MultiTenancySide but this seems to be coming through as NULL when it is from my second dbContext, which is decorated with [MultiTenancySide(MultiTenancySides.Tenant)]. Am I misunderstanding what the args.MultiTenancySide property does?

  • 0
    ismcagdas created
    Support Team

    Hi @4Matrix

    For MultiTenancySide, you need to pass it to your DbContextResolver somehow but in your case, I think you need to get the DbContextConcreteType and get its attribute via reflection and use it.

  • 0
    4Matrix created

    Thanks @ismcagdas, I did try decorating my second db context like this:

    [MultiTenancySide(MultiTenancySides.Tenant)]
        public class SecondDbContext : AbpDbContext
        {
            public virtual DbSet<CustomEntity> Stuff { get; set; }  
        ...
        }
    

    but it isnt picked up on my Custom Connection String Resolver:

      public override string GetNameOrConnectionString(ConnectionStringResolveArgs args)
            {
    
                if (args.MultiTenancySide == MultiTenancySides.Tenant)
                {
                    //This is never hit
                }
            ...
            }
    

    Any idea why? Is there something else I need to do? I could use the DbContextConcreteType but it seems like just using the MultiTenancySide would be much more efficient? Tenants will always have a connection string and will only access ONE Custom DbContext (In Addition to the AspNetZeroDbContext, for shared data lookups and user logins etc)

      public override string GetNameOrConnectionString(ConnectionStringResolveArgs args)
            {
                if (args["DbContextConcreteType"] as Type == typeof(SecondDbContext))
                {
                    var tenantCacheItem = _tenantCache.Get(_currentUnitOfWorkProvider.Current.GetTenantId().Value);
                    return tenantCacheItem.ConnectionString;
                }
                if (args["DbContextConcreteType"] as Type == typeof(ThirdDbContext))
                {
                    var tenantCacheItem = _tenantCache.Get(_currentUnitOfWorkProvider.Current.GetTenantId().Value);
                    return tenantCacheItem.ConnectionString;
                }
    
                return base.GetNameOrConnectionString(args);
            }
    
  • 0
    ismcagdas created
    Support Team

    Hi,

    When I look at MultiTenancySide attribute usage in the framework, it is always manually passed, I will check it deeply. In the meantime, you can use DbContextConcreteType as a workaround.