EF Migrations with Azure Pipeline Tasks

It turns out that it's a windows user issue. dotnet core 3 no longer has dotnet-ef built into dotnet and I knew this and installed dotnet-ef as global but apparently doing that is user specific on a machine. The dev that created the build pipeline needed to add dotnet-ef globally with their user on the same machine to get this working. Hope this helps someone else in a collaborative environment.

I had some confusion until I followed this article. With CI/CD pipelines there is a clear line between build (CI) and deploy (CD) which EF Migrations straddle. The build is like baking a pizza and is responsible to produce artifacts, while the release is like delivering the pizza and is responsible to deploy and install the artifacts on the target. Running ef database update in a AzureDevOps build pipeline is like trying to deliver a pizza that is still in the oven--it might work but probably going to burn the car and make folks hangry.

The build phase can't know where or when the artifacts will ever be used, so they need to be packaged with everything they might need. We also want the freedom to deploy to any supported environment without a new build so that we can react as needed (testing, high volume, disaster recovery, etc). Further, only the release phase will know the correct database type, its state and secrets until it is actually deployed to the target server, so any DB operations should be its responsibility.

The ef database update migration command does 2 tasks:

1. A CI task when it generates scripts that can be run on the DB using the connection settings in the active configuration.
2. A CD task when it applies the scripts to the target database.

So, we need to split the ef migration into different tasks that can be performed in the appropriate mode. Fortunately dotnet-ef provides the ef migrations script command to package the updates to a file that can be included as a build artifact. We also want to run the migrations only when needed and not wipe out data every time we commit code, so the idempotent flag is exactly what we need:

idempotent(computing) Describing an action which, when performed multiple times, has no further effect on its subject after the first time it is performed.

Awesome, but we still need to run the dotnet-ef command. As with the article I had issues with running the ef command as a dotnet task, so followed the advice and used a basic script command. The resulting build yaml is:

steps:- script: 'dotnet tool install --global dotnet-ef'  displayName: Install dotnet EF  - script: 'dotnet ef migrations script   --idempotent  --output migrations.sql --project pathto\aproject.csproj'  displayName: Create EF Scripts - task: CopyFiles@2  displayName: 'Copy EF Scripts to Staging'  inputs:    Contents: |     **\migrations.sql     TargetFolder: '$(build.artifactstagingdirectory)'    flattenFolders: true    - task: PublishBuildArtifacts@1  displayName: 'Publish Artifact'  inputs:    PathtoPublish: '$(build.artifactstagingdirectory)'

And then in the release, each stage needs to apply the script to the target database. The real power in this is that the same build can be pushed to multiple servers or environments where any custom configuration can be applied. In this example, a development Azure SQL instance:

- task: SqlAzureDacpacDeployment@1  displayName: 'Execute EF Migrations'  inputs:    azureSubscription: '$(azureSubscription)'    ServerName: '$(ServerName)'    DatabaseName: '$(DatabaseName)'    SqlUsername: '$(SqlUsername)'    SqlPassword: '$(SqlPassword)'    deployType: SqlTask    SqlFile: '$(System.DefaultWorkingDirectory)\**\migrations.sql'  

Now we can predictably deploy our build to any environment with the click of a button, and we have more time to eat the pizza (once it is done delivering).