Leveraging ADO Pipeline Tasks to create Entity Framework Deployment Script
Introduction
This post is part of the 2022 version of Azure Back to School, an Annual Community event For the Community by the Community.
Entity Framework is a great technology that enables developers to create database changes quickly and easily all while integrating with source control. Having databases under source control has long been a conundrum for developers, this is where entity framework can really help. However; just having entity framework isn’t enough. How can we leverage ADO to create entity framework deployment SQL scripts to make our deployment easier?
Creating rerunnable deployment scripts as part of the Azure DevOps (ADO) deployment pipelines is important so we don’t need to rely on developers exporting out SQL scripts or even worse manually applying the database changes from their machine.
PreReqs
- An entity framework project, I followed Getting Started with EF Core
- Found Visual Studio more useful then VS Code for this one
- Before starting advise check out my presentation on Multi-stage YAML Pipelines in Azure DevOps
Pipeline
The pipeline used for building the project needs to be flexible enough that the solution could have multiple other projects. Say you have the scenario of a Data Access Layer for your application that sits on top of the database. It only makes sense then that the APIs for the data access layer live in the same solution as the entity framework project. Now adhering to the Don’t Repeat Yourself (DRY) pattern, you could in theory leverage the same templates for both. How? Well let’s start with our entity framework needs first and the rest will fall into place.
pwsh_generate_ef_migration_script_task.yml
First need to to take the code required for the entity framework project to product a SQL migration script. From here we will then roll this up to the a job and a stage. Bear with me and we will account for the scenario of multiple projects.
parameters:
projectPath: ''
startUpProjectPath: ''
dropLocation: ''
projectName: ''
steps:
- task: PowerShell@2
displayName: 'Build EF Migration SQL Script'
inputs:
targetType: 'inline'
script: |
dotnet tool install dotnet-ef -g
dotnet ef migrations script --project ${{parameters.projectPath}} --startup-project ${{parameters.startUpProjectPath}} --no-transactions --output ${{parameters.dropLocation}}/${{parameters.projectName}}_dbScript.sql;
Something important here with entity framework, we need to tell the script what is the Start Up Project. This is required so take note then that this parameter will need to be passed in from any calling job.
entityframework_build_publish_job.yml
This job will be structured similar to a dotnetbuild job, the only difference is the inclusion of the pwsh_generate_ef_migration_script_task.yml
task. So something like this should work:
parameters:
solutionName: ''
buildConfiguration: 'Release'
projectName: ''
dotnetTest: true
zipAfterPublish: true
publishWebProject: true
publishArguments: ''
sdkVersion: ''
efMigrationScript: false
startUpProjectName: ''
jobs:
- job: build_publish_${{parameters.projectName}}
variables:
projectName: ${{replace(parameters.projectName,'_','.')}}
srcFilePath: 'src'
${{ if eq(parameters.solutionName, '')}} :
projectPath: '$(Build.SourcesDirectory)/${{ variables.srcFilePath }}/${{ variables.projectName }}'
testProjectPath: '$(Build.SourcesDirectory)/${{ variables.srcFilePath }}/${{ variables.projectName }}'
startUpProjectPath: '$(Build.SourcesDirectory)/${{ variables.srcFilePath }}/${{ parameters.startUpProjectName }}'
${{ else }} :
projectPath: '$(Build.SourcesDirectory)/${{ parameters.solutionName }}/${{ variables.srcFilePath }}/${{ variables.projectName }}'
testProjectPath: '$(Build.SourcesDirectory)/${{ parameters.solutionName }}/${{ variables.srcFilePath }}/${{ variables.projectName }}'
startUpProjectPath: '$(Build.SourcesDirectory)/${{ parameters.solutionName }}/${{ variables.srcFilePath }}/${{ parameters.startUpProjectName }}'
dropLocation: 'drop/${{ parameters.projectName }}'
steps:
- template: ../tasks/dotnet_sdk_task.yml
parameters:
sdkVersion: ${{ parameters.sdkVersion }}
- template: ../tasks/nuget_auth_task.yml
- template: ../tasks/dotnetcore_cli_task.yml
parameters:
command: 'build'
projectPath: '${{ variables.projectPath }}/**/*.csproj'
arguments: '--configuration ${{ parameters.buildConfiguration }}'
- ${{ if eq(parameters.dotnetTest, true) }}:
- template: ../tasks/dotnetcore_cli_task.yml
parameters:
command: 'test'
projectPath: '${{ variables.testProjectPath }}/**/*.csproj'
arguments: '--configuration ${{ parameters.buildConfiguration }} --collect "Code coverage"'
- template: ../tasks/dotnetcore_cli_publish_task.yml
parameters:
zipAfterPublish: ${{ parameters.zipAfterPublish}}
arguments: '--configuration ${{ parameters.buildConfiguration }} --output ${{variables.dropLocation}} ${{ parameters.publishArguments }}'
projectPath: '${{ variables.projectPath }}/**/*.csproj'
publishWebProject: ${{ parameters.publishWebProject }}
- template: ../tasks/pwsh_generate_ef_migration_script_task.yml
parameters:
projectPath: ${{ variables.projectPath }}
startUpProjectPath: ${{ variables.startUpProjectPath}}
dropLocation: ${{variables.dropLocation}}
projectName: ${{ variables.projectName}}
- template: ../tasks/ado_publish_pipeline_task.yml
parameters:
artifactName: ${{ parameters.projectName }}
targetPath: ${{variables.dropLocation}}
bicep_dotnet_build_stage.yml
So now what does the calling stage look like? We will need to conditionally call our entityframework_build_publish_job.yml if the project is an entity framework type. Also shouldn’t we want to loop through all the projects to determine this? For guidance on this check out my post on Advanced Azure DevOps YAML Objects or you can check out the video from the PreReqs.
parameters:
projectNamesConfigurations:
- projectName: ''
publishWebProject: true
dotnetTest: true
efMigrationScript: false
startUpProjectName: ''
solutionName: ''
environmentObjects:
- environmentName: 'dev'
regionAbrvs: ['cus']
templateFile: ''
templateDirectory: 'Infrastructure'
serviceName: ''
sdkVersion: ''
zipAppAfterPublish: true
publishArguments: ''
stages:
- stage: '${{ parameters.serviceName }}_build'
variables:
solutionPath: '$(Build.SourcesDirectory)/${{ parameters.solutionName }}/'
jobs:
- template: ../jobs/infrastructure_publish_job.yml
- ${{ each environmentObject in parameters.environmentObjects }} :
- ${{ each regionAbrv in environmentObject.regionAbrvs }} :
- template: ../jobs/bicep_whatif_env_job.yml
parameters:
environmentName: ${{ environmentObject.environmentName }}
templateFile: ${{ parameters.templateFile }}
templateDirectory: ${{ parameters.templateDirectory }}
serviceName: ${{ parameters.serviceName }}
regionAbrv: ${{ regionAbrv }}
- ${{ each projectNamesConfiguration in parameters.projectNamesConfigurations }} :
- ${{ if eq(projectNamesConfiguration.efMigrationScript, false) }} :
- template: ../jobs/dotnetcore_build_publish_job.yml
parameters:
solutionName: ${{ parameters.solutionName }}
projectName: ${{replace(projectNamesConfiguration.projectName,'.','_')}}
publishWebProject: ${{ projectNamesConfiguration.publishWebProject }}
sdkVersion: ${{ parameters.sdkVersion }}
dotNetTest: ${{ projectNamesConfiguration.dotnetTest }}
zipAfterPublish: $ {{ parameters.zipAppAfterPublish }}
publishArguments: ${{ parameters.publishArguments }}
efMigrationScript: ${{projectNamesConfiguration.efMigrationScript}}
startUpProjectName: ${{projectNamesConfiguration.startUpProjectName}}
- ${{ if eq(projectNamesConfiguration.efMigrationScript, true) }} :
- template: ../jobs/entityframework_build_publish_job.yml
parameters:
solutionName: ${{ parameters.solutionName }}
projectName: ${{replace(projectNamesConfiguration.projectName,'.','_')}}
publishWebProject: ${{ projectNamesConfiguration.publishWebProject }}
sdkVersion: ${{ parameters.sdkVersion }}
dotNetTest: ${{ projectNamesConfiguration.dotnetTest }}
zipAfterPublish: $ {{ parameters.zipAppAfterPublish }}
publishArguments: ${{ parameters.publishArguments }}
efMigrationScript: ${{projectNamesConfiguration.efMigrationScript}}
startUpProjectName: ${{projectNamesConfiguration.startUpProjectName}}
This stage will now loop through an object containing the individual project configurations. So what will our pipeline look like?
Pipeline.yml
parameters:
- name: environmentObjects
type: object
default:
- environmentName: 'dev'
dependsOn: ''
regionAbrvs: ['eus']
- name: templateFile
type: string
default: 'main'
- name: serviceName
type: string
default: 'adventureworks'
- name: solutionName
type: string
default: 'WebAPIEF'
- name: projectNamesConfigurations
type: object
default:
- projectName: 'WebAPIEF'
publishWebProject: false
azureServiceName: 'WebAPIEF'
dotnetTest: false
efMigrationScript: true
startUpProjectName: 'WebAPIEF'
- projectName: 'WebAPI'
publishWebProject: false
azureServiceName: 'webAPI'
dotnetTest: false
efMigrationScript: false
startUpProjectName: 'WebAPIEF'
- name: sdkVersion
type: string
default: '6.0.100'
- name: appName
type: string
default: 'adventureworks'
stages:
- template: stages/bicep_dotnet_build_stage.yml@templates
parameters:
environmentObjects: ${{ parameters.environmentObjects }}
templateFile: ${{ parameters.templateFile }}
serviceName: ${{ parameters.serviceName }}
projectNamesConfigurations: ${{ parameters.projectNamesConfigurations}}
sdkVersion: ${{parameters.sdkVersion}}
solutionName: ${{ parameters.solutionName }}
Conclusion
This isn’t easy! I recognize that so to help out I do have a git repo containing these templates and their associated tasks, TheYAMLPipelineOne
For next steps there are templates to get the deployment to work; however, I felt going over the build and the deployment might be too much information in one blog post.