Getting Started With EF Core Migrations

January 25, 2017

dotnet

Entity Framework (EF) migrations are a way to modify the database through different iterations of the software. Let’s have a play with these in EF Core in a web API project …

Models

Lets start with the following simple models to hold data about people:

public class Person
{
    public int PersonId { get; set; }
    public string Title { get; set; }
    public string FirstName { get; set; }
    public string Surname { get; set; }
    public ICollection EmailAddresses { get; set; }
    public ICollection

public class EmailAddress
{
    public int EmailAddressId { get; set; }
    public string Email { get; set; }
}

public class Address
{
    public int AddressId { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string TownOrCity { get; set; }
    public string Country { get; set; }
    public string Postcode { get; set; }
}

Adding Entity Framework

Now let’s add EF to our solution in project.json (I’m still using .NET core 1.0):

{
	"dependencies": {
	    ...,
	    "Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
	    "Microsoft.EntityFrameworkCore.SqlServer.Design": {
	      "version": "1.0.1",
	      "type": "build"
	    },
	    "Microsoft.EntityFrameworkCore.Tools": {
	      "version": "1.0.0-preview2-final",
	      "type": "build"
	    }
  	},
	"tools": {
		...,
		"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
	},
	...
}

DataContext

Let’s add the following data context:

public class PersonDataContext: DbContext
{
    public PersonDataContext() { }
    public PersonDataContext(DbContextOptions<PersonDataContext> options): base(options) { }
    public DbSet<Person> People { get; set; }
    public DbSet<Address> Addresses { get; set; }
    public DbSet<EmailAddress> EmailAddresses { get; set; }
}

Wiring EF up

To wire the app up to EF, first let’s add the following line to Startup.ConfigureServices:

services.AddDbContext<PersonDataContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

We then need to add the following line in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=People;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  ...
}

First migration

So, let’s have a go at our first migration …

Open a command prompt, browse to your solution directory and enter the following command:

dotnet ef migrations add init

After a second or so, our first migration will have been created in a Migrations folder in our solution:

Let’s have a look in the *_init.cs file. Cool, I can see an Up() method creating the 3 database tables and a Down() method droping the 3 tables. So, we have code that is going to create our schema which we can nicely source code control.

Let’s have a look at the PersonDataContextModelSnapshot.cs. As the name suggests, this looks like a copy of the schema – to faciliate the next migration generation.

But what will the generated migration SQL look like? Go back to the command prompt and enter:

dotnet ef migrations script

The SQL will be output to the screen (you can output to a file by using the command dotnet ef migrations script -o {filename}).

IF OBJECT_ID(N'__EFMigrationsHistory') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

CREATE TABLE [Person] (
    [PersonId] int NOT NULL IDENTITY,
    [FirstName] nvarchar(max),
    [Surname] nvarchar(max),
    [Title] nvarchar(max),
    CONSTRAINT [PK_Person] PRIMARY KEY ([PersonId])
);

GO

CREATE TABLE [Addresses] (
    [AddressId] int NOT NULL IDENTITY,
    [AddressLine1] nvarchar(max),
    [AddressLine2] nvarchar(max),
    [Country] nvarchar(max),
    [PersonId] int,
    [Postcode] nvarchar(max),
    [TownOrCity] nvarchar(max),
    CONSTRAINT [PK_Addresses] PRIMARY KEY ([AddressId]),
    CONSTRAINT [FK_Addresses_Person_PersonId] FOREIGN KEY ([PersonId]) REFERENCES [Person] ([PersonId]) ON DELETE NO ACTION
);

GO

CREATE TABLE [EmailAddresses] (
    [EmailAddressId] int NOT NULL IDENTITY,
    [Email] nvarchar(max),
    [PersonId] int,
    CONSTRAINT [PK_EmailAddresses] PRIMARY KEY ([EmailAddressId]),
    CONSTRAINT [FK_EmailAddresses_Person_PersonId] FOREIGN KEY ([PersonId]) REFERENCES [Person] ([PersonId]) ON DELETE NO ACTION
);

GO

CREATE INDEX [IX_Addresses_PersonId] ON [Addresses] ([PersonId]);

GO

CREATE INDEX [IX_EmailAddresses_PersonId] ON [EmailAddresses] ([PersonId]);

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20170124055720_init', N'1.0.1');

GO

The script creates a table called __EFMigrationsHistory which I didn’t create in my model. This will hold the list of migrations. You can see it adding our *_init migration at the bottom of the script.

I like that the primary keys have been correctly implemented as identities in the script. I like the foreign keys … I’m not liking the varchar(max) all over the place though!

Let’s fix the varchar(max) problem by adding the following code into PersonDataContext which specifies table names, field lengths and required fields in our schema:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>().ToTable("Person");
    modelBuilder.Entity<Person>().Property(p => p.Title).HasMaxLength(30);
    modelBuilder.Entity<Person>().Property(p => p.FirstName).HasMaxLength(50);
    modelBuilder.Entity<Person>().Property(p => p.Surname).HasMaxLength(100).IsRequired(true);

    modelBuilder.Entity<Address>().ToTable("Address");
    modelBuilder.Entity<Address>().Property(p => p.AddressLine1).HasMaxLength(300);
    modelBuilder.Entity<Address>().Property(p => p.AddressLine2).HasMaxLength(300);
    modelBuilder.Entity<Address>().Property(p => p.TownOrCity).HasMaxLength(100);
    modelBuilder.Entity<Address>().Property(p => p.Country).HasMaxLength(100);
    modelBuilder.Entity<Address>().Property(p => p.Postcode).HasMaxLength(10);

    modelBuilder.Entity<EmailAddress>().ToTable("EmailAddress");
    modelBuilder.Entity<EmailAddress>().Property(p => p.Email).HasMaxLength(100);
}

So, let’s see what the script looks like now. First we need to remove our last migration by entering the following command:

dotnet ef migrations script

You’ll notice the Migrations folder is now empty in our Visual Studio solution.

Now run the following command to generate the new migration and output the SQL script:

dotnet ef migrations add init
dotnet ef migrations script

IF OBJECT_ID(N'__EFMigrationsHistory') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

CREATE TABLE [Person] (
    [PersonId] int NOT NULL IDENTITY,
    [FirstName] nvarchar(50),
    [Surname] nvarchar(100) NOT NULL,
    [Title] nvarchar(30),
    CONSTRAINT [PK_Person] PRIMARY KEY ([PersonId])
);

GO

CREATE TABLE [Address] (
    [AddressId] int NOT NULL IDENTITY,
    [AddressLine1] nvarchar(300),
    [AddressLine2] nvarchar(300),
    [Country] nvarchar(100),
    [PersonId] int,
    [Postcode] nvarchar(10),
    [TownOrCity] nvarchar(100),
    CONSTRAINT [PK_Address] PRIMARY KEY ([AddressId]),
    CONSTRAINT [FK_Address_Person_PersonId] FOREIGN KEY ([PersonId]) REFERENCES [Person] ([PersonId]) ON DELETE NO ACTION
);

GO

CREATE TABLE [EmailAddress] (
    [EmailAddressId] int NOT NULL IDENTITY,
    [Email] nvarchar(100),
    [PersonId] int,
    CONSTRAINT [PK_EmailAddress] PRIMARY KEY ([EmailAddressId]),
    CONSTRAINT [FK_EmailAddress_Person_PersonId] FOREIGN KEY ([PersonId]) REFERENCES [Person] ([PersonId]) ON DELETE NO ACTION
);

GO

CREATE INDEX [IX_Address_PersonId] ON [Address] ([PersonId]);

GO

CREATE INDEX [IX_EmailAddress_PersonId] ON [EmailAddress] ([PersonId]);

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20170125045706_init', N'1.0.1');

GO

That’s much better!

Creating the database

So, let’s create the database by running the following command:

dotnet ef database update

After a few seconds, the database will have been created. You should be able to see the database in Visual Studio in the SQL Server Object Explorer

Second migration

Now, let’s implement a model change. We’re going to add a Created property to our Person model:

public class Person
{
    ...
    public DateTime Created { get; set; }
    ...
}

Let’s create that migration:

dotnet ef migrations add Person_Created

If you open up *_Person_Created.cs you’ll see the migration to add our new column.

Before updating our database again, let’s check our SQL migration script. We need to specify the migration we want to start from which in my case in 20170125045706_init:

dotnet ef migrations script 20170125045706_init

ALTER TABLE [Person] ADD [Created] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.000';

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20170125053501_Person_Created', N'1.0.1');

GO