Leveraging ADO Pipeline Tasks to create Entity Framework Deployment Script

September 27, 2022November 21, 2022 by John Folberth

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.