Multi-Tenancy with EF 7

[unlimitedcoders]

I am creating this issue as an extension to the one we have on #4410.

I am trying to create a multi-tenant app with 1 database and different schema for each tenant.
I followed the same approach mentioned in #4410 but it isn't working for me. Here is the code I am running.

I created a separate class called ContextClass to create my DBContext.

    public class ContextClass
    {        
        private static IModel GetCompiled(DbConnection connection, string tenantSchema)
        {           
            var builder = new ModelBuilder(new ConventionSet());           
            builder.Entity<CustomerBuilding>().ToTable("CustomerBuildings", tenantSchema);
            return builder.Model;
        }

        private static ConcurrentDictionary<string, IModel> modelCache = new ConcurrentDictionary<string, IModel>();
        public static CustomerDbContext Create(string tenantSchema, DbConnection connection)
        {
            try
            {
                IModel compiledModel;
                if (tenantSchema == null || tenantSchema.Trim().Length == 0) tenantSchema = "/";
                compiledModel = modelCache.GetOrAdd(tenantSchema, (t) => GetCompiled(connection, t));

                var optionsBuilder = new DbContextOptionsBuilder<CustomerDbContext>();
                optionsBuilder.UseSqlServer(connection);
                optionsBuilder.UseModel(compiledModel);

                return new CustomerDbContext(optionsBuilder.Options) { };
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void ProvisionTenant(string tenantSchema, DbConnection connection)
        {
            try
            {
                using (var ctx = Create(tenantSchema, connection))
                {
                    ctx.Database.EnsureCreated();
                    ctx.Database.Migrate();
                }               
            }
            catch (Exception ex)
            { var ed = ex.InnerException; }               
        }           
        }

From my client (i.e. the controller in my case), I am calling it like-

  using (var connection = new SqlConnection(@"Server = abc; Database=xyz; Trusted_Connection=True;"))
            {                
                ContextClass.ProvisionTenant("Tenant1", connection);
            }

My CustomerDBContext is like-

public class CustomerDbContext : DbContext
    {
        public CustomerDbContext(DbContextOptions<CustomerDbContext> options) : base(options)
        {

        }
        public DbSet<CustomerBuilding> CustomerBuildings { get; set; }
    }

On the Startup code-

services.AddScoped<CustomerDbContext>();

I want this approach to create the same tables that I have in my CustomerDBContext but in different schemas whenever I called the Create method passing on the schemaname.

Whenever I am calling EnsureCreated or Migrate, it doesn't create any tables. I tried both by creating the migrations and without them.

I also tried another approach where I used the OnModelCreating and passing on the SchemaName like -

                modelBuilder.Entity<CustomerBuilding>(x =>
                {
                    x.ToTable("CustomerBuilding", "SchemaName");
                });

Though with this approach, I was able to create tables in my schema the first time using EnsureCreated but it didn't create tables in other schemas when I called Migrate with different schema subsequently.

[rowanmiller]

@unlimitedcoders couple of things to point out, though I'm not sure that they are the root cause of your problems...

---

services.AddScoped<CustomerDbContext>();

This won't work, because DI can't just create instances of your context... it needs to use your ContextClass.Create() method. But I don't think it's causing you any issues since it sounds like you are calling ContextClass.Create() in your controllers and not resolving the context from DI anyway. If you did want everything to be in DI, then you could services.AddSingleton<ContextClass>() and then resolve that in your controllers and use it to create context instances.

---

ctx.Database.EnsureCreated();
ctx.Database.Migrate();

These two are mutually exclusive and should not both be used:

• EnsureCreated() checks if the database exists and creates the database and the schema for your model if it does not.
  • Migrate() applies your migrations to the database. If you are using migrations, then by default they would have schema names hard coded in them. So unless you have done work to make them dynamically chose the schema then this will always just apply migrations to whatever schema they have hard coded in them.

---

Down to the root cause of your issue... EnsureCreated() doesn't check if the tables for the current model exists, it just checks if the actual database exists. So for the first tenant, it creates the database and the schema for the tenant. But for subsequent tenants, it sees that the database exists and does nothing.

The methods on Database are designed to apply to all databases, not just relational ones. If you want to get some relational database specific building block methods, you can get the RelationalDatabaseCreator service and call methods directly on that.

Here is some code that will create the tables for the current model. There are also methods to check for database existence, etc.

var serviceProvider = context.GetInfrastructure<IServiceProvider>();
var creator = serviceProvider.GetService<IRelationalDatabaseCreator>();
creator.CreateTables();

Note that EF Core does not have any mechanism to check if the database contains any tables for the current model... so if you need to check if the tables for the current model already exist then you'll need to do this with ADO.ENT.

[rowanmiller]

Feel free to reopen if I didn't answer your question

[unlimitedcoders]

Thanks @rowanmiller. CreateTables() does the trick.