2 databases in same project

Question

Hi,

I am developing a ASP.NET 6 MVC application that uses Identity which I already implemented manually. After installing and setting the necessary stuff, I added the first migration (add-migration migration-name) and updated the database (update-database).

Everything worked just fine and now I have my Identity tables created and working correctly. However, the web application will be installed twice in 2 different websites and each one will have its own database and each database has its own name. All this in one Windows Server web server.

There are already 2 databases created in my PC, one with Identity tables included and the other does not include them yet. So I would need to add a second connection string for the second database and run a migration and database update again based on second connection string. But I suppose this would interfere with the first migration data for the first database.

Migration data for the the first database is located in folder Data/Migrations. I have thought about running migration for second database and specify another folder, Data/MigrationsDB2 for example. However, I do not know how it affect my project. Or maybe this is not a good choice.

What can I do in this case?

Regards,
Jorge Maldonado

Answer 1

Hi @JORGE MALDONADO BARRERA ,

There are already 2 databases created in my PC, one with Identity tables included and the other does not include them yet. So I would need to add a second connection string for the second database and run a migration and database update again based on second connection string. But I suppose this would interfere with the first migration data for the first database.

To use multiple Database in one application, you can use multiple context to create one DbContext type per database. Like this:

builder.Services.AddDbContext<FirstDbContext>(options =>  
    options.UseSqlServer("first datatbase connection string"));  
builder.Services.AddDbContext<SecondDbContext>(options =>  
    options.UseSqlServer("second datatbase connection string"));  

Then, when execute the EF core migrations commands, you can specify the context type, like this:

Add-Migration InitialCreate -Context FirstDbContext -OutputDir Migrations\FirstDbMigrations  
Add-Migration InitialCreate -Context SecondDbContext -OutputDir Migrations\SecondDbMigrations  

Since we have specified the context type, when execute the migration command, it will not interfere other context and database.

Besides, you can also use one context with multiple database, by using this method, you also need to set the argument to specify the database connection. In my opinion, I prefer to the multiple context.

More detail information, see EF core Migrations with Multiple Providers.

Are you talking about the LocalDB used in the development environment?

No matter the Local DB, but also the SQL Server database or Azure, SQL database, they should also work. In the development environment you can use the Local DB or real database, after publishing the application to the server, just change the database connection string to use the real database.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion