Entity Framework could be a logical choice when building a .NET Core API that needs to talk to a database. In this blog, I’ll show you how to get started with EF and Migrations. We will create the models, the DbContext, and the migrations needed to upgrade your database when introducing changes. We will also see how we can deploy those changes in your Azure DevOps pipeline on each release.

This blog is part of a larger series of blogs in which I explain how to set up a database-per-tenant architecture. Most of what this blog touches can still be used without reading the whole series.

TL;DR: All code, templates, and pipelines can be found in my repository.

Create the project

In most projects, you probably want to split your data layer from your API. That’s what we’re going to do in this example. Use the following commands to create the two separate projects.

dotnet new webapi -n DatabasePerTenant.WebApi

dotnet new classlib -n DatabasePerTenant.Data.Catalog

The second project needs a few NuGet packages. Install them using these commands:

    dotnet add package Microsoft.EntityFrameworkCore
    dotnet add package Microsoft.EntityFrameworkCore.Relational
    dotnet add package Microsoft.EntityFrameworkCore.SqlServer

Time to add some entities that will represent our database tables. The ones I use here contain the data in my database-per-tenant architecture and make up the Catalog database.

using System;
using System.Collections.Generic;

namespace DatabasePerTenant.Data.Catalog.Model
{
    public partial class Customer
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public List<Tenant> Tenants { get; set; }
        public DateTime LastUpdated { get; set; }
    }

    public partial class Tenant
    {
        public int TenantId { get; set; }
        public byte[] HashedTenantId { get; set; }
        public string TenantName { get; set; }
        public string DatabaseName { get; set; }
        public int ElasticPoolId { get; set; }
        public ElasticPool ElasticPool { get; set; }
        public int CustomerId { get; set; }
        public Customer Customer { get; set; }
        public DateTime LastUpdated { get; set; }
    }

    public partial class ElasticPool
    {
        public int ElasticPoolId { get; set; }
        public string ElasticPoolName { get; set; }
        public int ServerId { get; set; }
        public Server Server { get; set; }
        public DateTime LastUpdated { get; set; }
    }

    public partial class Server
    {
        public int ServerId { get; set; }
        public string ServerName { get; set; }
        public List<ElasticPool> ElasticPools { get; set; }
        public DateTime LastUpdated { get; set; }
    }
}

The next step is to create our DbContext. It’s our entry-point into our database. Mine also contains the configuration per entity, something you could also do using annotations on the entities. The fluent API used here is just a bit more powerful.

using Microsoft.EntityFrameworkCore;
using DatabasePerTenant.Data.Catalog.Model;

namespace DatabasePerTenant.Data.Catalog
{
    public partial class CatalogDbContext : DbContext
    {
        public virtual DbSet<Customer> Customers { get; set; }
        public virtual DbSet<Tenant> Tenants { get; set; }
        public virtual DbSet<ElasticPool> ElasticPools { get; set; }
        public virtual DbSet<Server> Servers { get; set; }

        public CatalogDbContext(DbContextOptions<CatalogDbContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>(entity =>
            {
                entity.HasKey(e => e.CustomerId);

                entity.Property(e => e.CustomerName)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.LastUpdated)
                    .IsRequired();
            });

            modelBuilder.Entity<Tenant>(entity =>
            {
                entity.HasKey(e => e.TenantId);

                entity.Property(e => e.TenantId)
                    .ValueGeneratedNever();

                entity.Property(e => e.HashedTenantId)
                   .IsRequired();

                entity.Property(e => e.DatabaseName)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.TenantId).HasMaxLength(128);

                entity.Property(e => e.LastUpdated)
                    .IsRequired();
            });

            modelBuilder.Entity<ElasticPool>(entity =>
            {
                entity.HasKey( e => e.ElasticPoolId );

                entity.Property(e => e.ElasticPoolName)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.LastUpdated)
                    .IsRequired();
            });

            modelBuilder.Entity<Server>(entity =>
            {
                entity.HasKey(e => e.ServerId);

                entity.Property(e => e.ServerName)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.LastUpdated)
                    .IsRequired();
            });
        }
    }
}

EF Migrations

It’s time to generate our first migration. We need to install a command-line tool to do that:

Install dotnet-ef using 'dotnet tool install --global dotnet-ef'

The fact that we added our DbContext in a separate project does make things a little harder here. The command used to generate the migrations needs a project that has a runtime associated. The project containing our DbContext is a class library that cannot be run by itself. You would run into the following error when trying to generate the migration using ‘dotnet-ef migrations add InitialMigration’:

Startup project ‘MyCodeCamp.Data.csproj’ targets framework ‘.NETStandard’. There is no runtime associated with this framework, and projects targeting it cannot be executed directly. To use the Entity Framework Core .NET Command-line Tools with this project, add an executable project targeting .NET Core or .NET Framework that references this project, and set it as the startup project using –startup-project; or, update this project to cross-target .NET Core or .NET Framework. For more information on using the EF Core Tools with .NET Standard projects, see https://go.microsoft.com/fwlink/?linkid=2034781

The tooling supports pointing to a project that does have a runtime using the –startup-project flag. An additional flag, –project, would then be needed to point to the project that contains the DbContext (or you would need to run that command from within the folder that contains that project file). So, for our demo here, we could run the command like:

dotnet-ef migrations add InitialMigration --project DatabasePerTenant.Data.Catalog --startup-project DatabasePerTenant.WebApi

What then happens is that the app you’re pointing to gets run. That’s fine for a small demo application, but you’ll run into all sorts of problems when that project gets more complicated. For example, imagine that your app uses Azure App Configuration and KeyVault, and you use a Managed Identity to authenticate them. That means that whenever you want to add a migration, you have to authenticate with KeyVault. Luckily there’s a better and easier way to do things.

A separate project for running your migrations

The answer here is to add an additional project that can run, so a console app, for example, which sole purpose is to generate our migrations. We can add this project with the following command:

dotnet new console -n DatabasePerTenant.Data.MigrationApp

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

dotnet add DatabasePerTenant.Data.MigrationApp/DatabasePerTenant.Data.MigrationApp.csproj reference DatabasePerTenant.Data.Catalog/DatabasePerTenant.Data.Catalog.csproj

While adding a migration, the DbContext needs to be configured before migrations can be created. The configuring is something we will, later on, do per request and not in the constructor of the DbContext like you usually would. Therefore, we need to supply something called an IDesignTimeDbContextFactory to let the migrator know how to instantiate the DbContext. Add the following class to the MigrationApp project. You can leave the connection string as-is. It won’t actually be used while creating the migration. You will only need to specify a valid one if you want to update your database from your local machine using the update command. I’m using Azure DevOps to do that.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using DatabasePerTenant.Data.Catalog;

namespace DatabasePerTenant.Data.Catalog
{
    public class CatalogContextFactory : IDesignTimeDbContextFactory<CatalogDbContext>
    {
        public CatalogDbContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<CatalogDbContext>();
            optionsBuilder.UseSqlServer("Data Source=localhost;Initial Catalog=catalog;persist security info=True;Integrated Security=SSPI;");

            return new CatalogDbContext(optionsBuilder.Options);
        }
    }
}

With that in place we can finally add our first migration:

dotnet-ef migrations add InitialMigration --project DatabasePerTenant.Data.Catalog --startup-project DatabasePerTenant.Data.MigrationApp

Running the migration in our Azure DevOps pipeline

The last step in this story is running the migration during a release in our Azure DevOps pipeline. Below you will find the complete YAML file to do that. I’ve divided the pipeline into two stages. The first stage will build the code and create the EF Migration SQL file. The second stage contains the execution of the migrations and the deployment of the API. I’ll discuss some of the important parts in more detail.

Generate the migration SQL file

The first four steps in this pipeline are pretty standard. The API gets build and published. I’ve then added a step to set the .Net Core version to be used. That version is installed on the Azure DevOps agent but not used by default. The step after that, to install the dotnet-ef tool, requires this very new version to work. Next, we can run the migration command. It looks very similar to what we’ve done locally on the command-line. However, this time, we added the parameter ‘script’ to instruct to cli to create a SQL-file instead of code. The last step in this build stage is to publish our new SQL-file for later use.

- task: UseDotNet@2
  inputs:
    version: '3.1.200'
- task: DotNetCoreCLI@2
  displayName: 'Initialize EntityFrameworkCore'
  inputs:
    command: custom
    custom: tool
    arguments: 'install --global dotnet-ef'
- task: DotNetCoreCLI@2
  displayName: 'Create migration'
  inputs:
    command: custom
    custom: ef
    arguments: 'migrations script --project DatabasePerTenant.Data.Catalog/ --startup-project DatabasePerTenant.Data.MigrationApp --context CatalogDbContext -i -o $(Build.ArtifactStagingDirectory)/sql/migration.sql'
    workingDirectory: $(Build.SourcesDirectory)
- task: PublishBuildArtifacts@1
  displayName: 'Publish SQL Script'
  inputs:
    PathtoPublish: '$(Build.ArtifactStagingDirectory)/sql'
    ArtifactName: 'sql'
    publishLocation: 'Container'

Perform the migration

Performing the migration is done in the next stage. There are two important steps here. The second step is where we run the migration. To do that, we need a connection to the database. That requires a username and password, which I have stored in KeyVault. My API uses a Managed Identity at runtime to retrieve that information. During the build, we can use a task to get the needed secrets. Remember to grant the identity you use to run your pipeline read access to KeyVault.

- task: AzureKeyVault@1
  displayName: 'Fetch sql username and password'
  inputs:
    azureSubscription: '$(AzureSubscriptionName)'
    KeyVaultName: '$(KeyVaultName)'
    SecretsFilter: 'CatalogDbPassword,CatalogDbUserName'
    RunAsPreJob: false
- task: SqlAzureDacpacDeployment@1
  inputs:
    azureSubscription: '$(AzureSubscriptionName)'
    AuthenticationType: 'server'
    ServerName: '$(ServerName)'
    DatabaseName: '$(DatabaseName)'
    SqlUsername: '$(CatalogDbUserName)'
    SqlPassword: '$(CatalogDbPassword)'
    deployType: 'SqlTask'
    SqlFile: '$(System.DefaultWorkingDirectory)/sql/migration.sql'
    IpDetectionMethod: 'AutoDetect'

YAML build pipeline

Here’s the complete yaml file:

# ASP.NET Core
# Build and test ASP.NET Core projects targeting .NET Core.
# Add steps that run tests, create a NuGet package, deploy, and more:
# https://docs.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- master

pr: none

pool:
  vmImage: 'ubuntu-latest'

variables:
  buildConfiguration: 'Release'
  System_AccessToken: $(System.AccessToken)

stages:
- stage: 'BuildTestPublish'
  displayName: 'Build and Publish'
  jobs: 
    - job: 'BuildTestPublish'
      steps:
      - task: DotNetCoreCLI@2
        displayName: 'Restore'
        inputs:
          command: 'restore'
          projects: '**/*.csproj'
          feedsToUse: 'select'
      - task: DotNetCoreCLI@2
        displayName: 'Build'
        inputs:
          command: 'build'
          projects: '**/*.csproj'
      - task: DotNetCoreCLI@2
        displayName: 'Publish'
        inputs:
          command: publish
          publishWebProjects: True
          arguments: '--configuration $(BuildConfiguration) --output $(Build.ArtifactStagingDirectory)/api'
          zipAfterPublish: True
      - task: PublishBuildArtifacts@1
        displayName: 'Publish api'
        inputs:
          PathtoPublish: '$(Build.ArtifactStagingDirectory)/api'
          ArtifactName: 'api'
          publishLocation: 'Container'
      - task: UseDotNet@2
        inputs:
          version: '3.1.200'
      - task: DotNetCoreCLI@2
        displayName: 'Initialize EntityFrameworkCore'
        inputs:
          command: custom
          custom: tool
          arguments: 'install --global dotnet-ef'
      - task: DotNetCoreCLI@2
        displayName: 'Create migration'
        inputs:
          command: custom
          custom: ef
          arguments: 'migrations script --project DatabasePerTenant.Data.Catalog/ --startup-project DatabasePerTenant.Data.MigrationApp --context CatalogDbContext -i -o $(Build.ArtifactStagingDirectory)/sql/migration.sql'
          workingDirectory: $(Build.SourcesDirectory)
      - task: PublishBuildArtifacts@1
        displayName: 'Publish SQL Script'
        inputs:
          PathtoPublish: '$(Build.ArtifactStagingDirectory)/sql'
          ArtifactName: 'sql'
          publishLocation: 'Container'
- stage: 'DeployTotest'
  displayName: 'Deploy to test'
  pool:
   vmImage: 'windows-latest'
  jobs:
    - job: 'Deploy'
      steps:
      - checkout: Client
        path: 'client' 
      - task: DownloadBuildArtifacts@0
        displayName: 'Download api artifacts'
        inputs:
          buildType: 'current'
          downloadType: 'single'
          artifactName: 'api'
          downloadPath: '$(System.DefaultWorkingDirectory)'
      - task: DownloadBuildArtifacts@0
        displayName: 'Download sql artifacts'
        inputs:
          buildType: 'current'
          downloadType: 'single'
          artifactName: 'sql'
          downloadPath: '$(System.DefaultWorkingDirectory)'
      - task: AzureKeyVault@1
        displayName: 'Fetch sql username and password'
        inputs:
          azureSubscription: '$(AzureSubscriptionName)'
          KeyVaultName: '$(KeyVaultName)'
          SecretsFilter: 'CatalogDbPassword,CatalogDbUserName'
          RunAsPreJob: false
      - task: SqlAzureDacpacDeployment@1
        inputs:
          azureSubscription: '$(AzureSubscriptionName)'
          AuthenticationType: 'server'
          ServerName: '$(ServerName)'
          DatabaseName: '$(DatabaseName)'
          SqlUsername: '$(CatalogDbUserName)'
          SqlPassword: '$(CatalogDbPassword)'
          deployType: 'SqlTask'
          SqlFile: '$(System.DefaultWorkingDirectory)/sql/migration.sql'
          IpDetectionMethod: 'AutoDetect'
      - task: AzureRmWebAppDeployment@4
        displayName: 'Publish web app to Azure'
        inputs:
          ConnectionType: 'AzureRM'
          azureSubscription: '$(AzureSubscriptionName)'
          appType: 'webAppLinux'
          WebAppName: '$(WebAppName)'
          packageForLinux: '$(System.DefaultWorkingDirectory)/**/*.zip'
          RuntimeStack: 'DOTNETCORE|3.1'