How to add-migrations for auto-generated databases?

Question

I have an application which contains to context classes. One is master context class and second is sub context class. When I run the application for the first time the master database gets generated. And whenever I create a new user, a database related to that user gets generated using sub context class. For example, if I create 10 users then 10 databases will get generated using sub context class like sub_db_userId.

The problem is enabling migrations in this complex structure. I know some people would say it's not appropriate to create new database use foreign key relation but I have to work on requirements.

I found this thread very helpful in finding how to enable-migrations for separate context classes but in my scenario, it does not apply changes to existing databases because of database name is associated with user ids. Rather applying changes to existing child databases it creates new database without user id like this sub_db_. How can I solve this issue?

The way I am creating the new database for every user is given below.

My context classes:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection", throwIfV1Schema: false) {}
}

public class SubDbContext : DbContext
{
    public DbSet<Country> Countries { get; set; }
    public DbSet<City> Cities { get; set; }

    public SubDbContext() : base("SubDatabaseConnection")
    {
    }

    public SubDbContext(string connectionString) : base(connectionString)
    {
        Database.SetInitializer<SubDbContext>(new 
                           CreateDatabaseIfNotExists<SubDbContext>());
    }
}

Connection strings:

<add name="DefaultConnection" 
     connectionString="Data Source=.\SQLExpress;Initial Catalog=master_db;Integrated Security=True" 
     providerName="System.Data.SqlClient" />
<add name="SubDatabaseConnection" 
     connectionString="Data Source=.\SQLExpress;Initial Catalog={0};Integrated Security=True" 
     providerName="System.Data.SqlClient" />
</connectionStrings>

I am using {0} for formatting purposes. How can I enable migrations and apply changes to existing databases?

Modifications: I am linking tables to database in Register action like this:

SubDbContext newContext = new SubDbContext(string.Format(userDatabase, "sub_db_" + userId));
newContext.Countries.FirstOrDefault();
newContext.Cities.FirstOrDefault();

Do you know all the user specific databases that possibly exist in e.g. your "master" database (not to be confused with the master database of sql-server)? — earloc, Jun 11 '17 at 19:59
Sorry I didn't get you? All the users exist in my master database. — John Adam, Jun 11 '17 at 20:14

Majid Parvin · Answer 1 · 2017-06-12T08:52:04.333

Your DbContext for all your sub-databases is SubDbContext. So you must enable migration based on one of them. then put this code as Configuration class:

internal sealed class Configuration : DbMigrationsConfiguration<SubDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
    }
}

and put this code into the SubDbContext constructor:

Database.SetInitializer<SubDbContext>(new MigrateDatabaseToLatestVersion<SubDbContext, Configuration>());

it means, every database when found a new manual added Migration, then try to migrate it.

and also you can use this approache too:

write this code in the Application_Start:

var context = new SubDbContext("your generated connection string");
var initializeMigrations = new MigrateDatabaseToLatestVersion<SubDbContext, Configuration>();

initializeMigrations.InitializeDatabase(context);

I hope you find it helpful.

How to add-migrations for auto-generated databases?

1 Answers1