If you want to automate Migrations to your database, and who wouldn’t want to, there are a couple of things and some potential issues you’ll need to consider.

The first thing is, you want to be able to prepare your migrations as part of your build process, however, you will ideally prefer to apply these as part of your release pipeline in what would be the most appropriate timing, certainly not on the back of a build.

Whilst applying a migration itself is not the difficulty, what is the best way of implementing this in your CI/CD Pipeline when it comes to Entity Framework (EF) does require some consideration. In honesty, there is no right/wrong way in this regard but, I will say, that applying EF Core migrations is a lot easier than the fully-blown .NET Framework equivalent which typically requires the assistance of Package Manager within Visual Studio.

So, ideally, our objective here is to obtain an artifact which our release pipeline can use.

The way i’m going to demonstrate how this can be adopted, for what is described as a ‘code-first’ approach to updating the database schema, is by using the dotnet cli.

For me, there are two areas of consideration:

1) the Initial Creation of the migrations; and,
2) the ongoing update of migrations with Idempotence, allowing for repeated runs and updates only when required migrations are missing.

1 – The Initial_Create

In order to get our migrations under way, we can use dotnet ef database update to perform the initial migrations and the so-called history table.

The objective of this command is to run the Initial_Create migration. If you read the EF Core tools reference, you will see that the dotnet ef database update command contains a --connection option in order to define the connection to the database.

Known issue with –connection…

However, in some EF Core versions prior to 5.0, you’ll find that this option is not compatible, offering an error similar to the following:

Unrecognized option '--connection'

or

--connection <CONNECTION> option of database update is not supported

The version I’ve experienced this with is 3.1.5.

… resolved

Here’s how to get round this issue. Quite simply, it is to update the appsettings.json file with the Connection String to the database. You can create a PowerShell task in the Build which updates the appsettings.json file with the database connection string, and then immediately runs the Initial_Create. 

dotnet tool install --global dotnet-ef
$content = gc .\appsettings.json -raw | ConvertFrom-Json
$content.ConnectionStrings.DefaultConnection=$(SQLDbConnString)
$content | ConvertTo-Json | Set-Content .\appsettings.json
dotnet ef database update $(Initial_Create)

Once it has been run, the schema will look something like this:

If we take a peek at the table, we can see that our first run has been successful:

This approach is intended as a run once, but by defining it in a build, it allows for any reruns (more akin for dev/test environments), rebuilds, or newly provisioned databases which require this same migration. It’s just a case of feeding the appropriate database connection string on each occasion, by way of the variable $(SQLDbConnString).

In addition, you can feed to the initial migration ID via the $(Initial_Create) variable and the Working Directory (location in the code repository where your data project is located) to get things going for that initial step across multiple databases, making this Build definition re-usable for all your EF Core migrations on any project.

Here’s what the full YAML definition for this build looks like:

steps:
- powershell: |
   dotnet tool install --global dotnet-ef
   
   $content = gc .\appsettings.json -raw | ConvertFrom-Json
   $content.ConnectionStrings.DefaultConnection=$(SQLDevDbConnString)
   $content | ConvertTo-Json | Set-Content .\appsettings.json
   
   dotnet ef database update $(Initial_Create)
  workingDirectory: MyProjectDirectory/data
  displayName: 'PowerShell Script'

Even though we could use this approach to run all future migrations on the database, the downside would be that migration updates would be run immediately at the time of the build, which is undesirable. The objective here is to run migrations in the future via the release at the appropriate time.

2 – Ongoing EF Core Migrations

Now that the initial migrations have been run, a facility is required to manage ongoing migrations, keeping the dbcontext consistent.

As already mentioned, the desire is to have an artifact resulting from a build which can be lined up and released at the appropriate occasion.

To produce this, the dotnet ef migrations script command comes in to play. This is where a SQL script gets produced which be used later to run the migrations against the database. Again, you can use a PowerShell task in the pipeline for this:

dotnet tool install --global dotnet-ef
dotnet ef migrations script --output $(Build.ArtifactStagingDirectory)/migrations_script.sql --context $(SQLDbContext) --idempotent

Note the variable $(SQLDbContext) which has been assigned as a pipeline variable; the output of the command is a file called migrations_script.sql; and, the --idempotent option meaning that the script can be used on a database at any migration i.e. can be run on an existing database and only missing migrations are applied.

To complete the build phase, the last step is to publish the resulting migrations_script.sql as an artifact.

The YAML definition for this build is as follows:

pool:
  name: Azure Pipelines
variables:
  SQLDbContext: 'MyProjectDbContext'
steps:
- powershell: |
   dotnet tool install --global dotnet-ef
   dotnet ef migrations script --output $(Build.ArtifactStagingDirectory)/migrations_script.sql --context $(SQLDbContext) --idempotent
  workingDirectory: MyProject/data
  displayName: 'PowerShell Script'
- task: PublishBuildArtifacts@1
  displayName: 'Data Migrations'
  inputs:
    ArtifactName: 'Data_Migrations'

Applying the EF Core Migrations

For this final stage of the puzzle, being the release, the focus here is to run the SQL script as an Azure SQL Task

steps:
- task: SqlAzureDacpacDeployment@1
  displayName: 'Azure SQL SqlTask'
  inputs:
    azureSubscription: 'DevTest'
    AuthenticationType: connectionString
    ConnectionString: '$(SqlDevDbConnString)'
    deployType: SqlTask
    SqlFile: '$(System.DefaultWorkingDirectory)/Build/Artifact_Folder/migrations_script.sql'

The result, is a nicely prepared database ready for the off.

Further Considerations

If, over time, the number of defined migrations grows to a proportion where checking all of them is not necessary, there is an option in the dotnet ef migrations script command that allows you to define the start and finish migration with which to apply. This is denoted by the following:

dotnet ef update script <from> <to> --output --context --idempotent

<from> defines the starting migration, and should stipulate the migration name or ID.

<to> defines the ending migration, defaulting to the last if not specified.