Updating databases in Entity Framework for development and production environments in ASP.NET Core

Question

How do I apply EF migrations to multiple databases, referencing either a Development or Production environment as required?

TL;DR

I'm developing an ASP.NET Core MVC application using Entity Framework that references different databases for Development and Production. Both databases are hosted Azure SQL databases.

The application at the moment is running on IIS Express on my Development machine, but I will deploy Production to an Azure App Service. I'm using Entity Framework and migrations to update the database design.

I've applied this answer to use a different database connection string for either the Development or Production launch configurations, and this seems to be working. The connection strings are then stored in appsettings.Development.json or appsettings.Production.json.

When I select the DEV launch configuration, I am able to use the EF cli command dotnet ef database update in order to apply my migrations to the development database. Works fine.

However, I cannot work out how to tell Visual Studio to apply each migration to the production database when I run under the PROD launch configuration.

Running the command dotnet ef database update after changing the launch configuration continues referencing the development database, and nothing changes. I'm not surprised by this - I haven't told Visual Studio where to find the other database. But I also can't work out how to do this.

Is there a way to change the referenced database, based on the launch configuration or environment variables? Or some other convenient way?

launchSettings.json

{
  "iisSettings": {
    "windowsAuthentication": false,
    "anonymousAuthentication": true,
    "iisExpress": {
      "applicationUrl": "http://localhost:57961",
      "sslPort": 44320
    }
  },
  "profiles": {
    "IIS Express (dev)": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "IIS Express (prod)": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Production"
      }
    }
  }
}

score 2 · Accepted Answer · answered May 31 '22 at 06:55

2

According to the documentation, you can specify the connection string as an extra parameter:

--connection: The connection string to the database. Defaults to the one specified in AddDbContext or OnConfiguring.

So this command will let you specify which database you are targeting:

dotnet ef database update --connection "<sql connection string>"

answered May 31 '22 at 06:55

Thomas

24,234
6
81
125

According to the docs, --connection is "The connection string to the database. Defaults to the one specified in AddDbContext or OnConfiguring." In `Startup.cs` `ConfigureServices()` method I'm already using `services.AddDbContext` to retrieve the production database connection string from `appsettings.production.json` so presumably ef should be using this already (when I step through the application, the correct connection string is retrieved). But the migrations are only going to development. – khargoosh May 31 '22 at 07:31
the tool doesn't know about your launch settings file. not sure if specifying the startup project + configuration mode can help tho. What i mean is that you can just run the command with the prod connectionstring to update your production db – Thomas May 31 '22 at 07:35
I believe you, but I don't understand the docs then when they say "Defaults to the one specified in AddDbContext" when that method uses the `Configuration.GetConnectionString("DBConnectionString")` parameter! – khargoosh May 31 '22 at 07:37
I think its default to whatever you have in the `appsettings.json` file. Not sure if you can say target the `appsettings.Production.json` file. – Thomas May 31 '22 at 07:40
I need to test this further. I don't have any DB Connection String in `appsettings.json` so there is some misalignment somewhere here... – khargoosh May 31 '22 at 07:54
I think I found the problem. I'm running VS 2019 and Core 3.1 - EF version only 3.17. The docs say that the --connection switch only added in EF version 5.0 - but I can't upgrade EF because latest version not compatible with Core 3.1. So I'm upgrading to VS 2022 now and will update all and test again. – khargoosh May 31 '22 at 08:17
1

Thanks @Thomas. After upgrading to .NET 6 and EF6 I was able to use the `--connection` switch and select the database. Then I scripted each into a CLI .cmd which I call from the Developer Powershell or CLI window. – khargoosh Jun 01 '22 at 05:38

Updating databases in Entity Framework for development and production environments in ASP.NET Core

1 Answers1