How to use EF Core Migrations
Tags: csharp, dotnet, efcore, migrations
Published: 2023-02-15 08:41:19

The EF Core Migrations feature provides a way to incrementally update the database schema to keep in sync with your application's data model, while still preserving existing data in your database.

In this article I want to give you an overview of the most common tasks you might face when working with Migrations in EF Core.

## Content
- [Add a migration](#add-a-migration)
    - [Customize migration code](#customize-migration-code)
        - [Column renames](#column-renames)
        - [Adding raw SQL](#adding-raw-sql)
        - [Arbitrary changes via raw SQL](#arbitrary-changes-via-raw-sql)
- [Remove a migration](#remove-a-migration)
- [Execute migrations](#execute-migrations)
    - [Basic Usage](#basic-usage)
    - [With From](#with-from)
    - [With From and To](#with-from-and-to)

## Add a migration
After you have created your models and your EF Core context, or after you have made changes to them, you can add a migration as follows.

```bash
dotnet ef migrations add "Add_Created_Column_To_Customers"
```
**Tip:** Try to name your migration like a Git commit message

### Customize migration code
Sometimes it might be necessary to customize a migration. For this you can update the **Up** or **Down** method in the migration file.

#### Column renames
To rename a property from `Name` to `FullName`, you need to replace the generated code

```csharp
migrationsBuilder.DropColumn(
    name: "Name",
    table: "Customers"
);

migrationsBuilder.AddColumn<string>(
    name: "FullName",
    table: "Customers",
    nullable: true
);
```

with the following. This is due to EF Core not knowing if you intended to drop and create a new column or to rename a column.

```csharp
migrationsBuilder.RenameColumn(
    name: "Name",
    table: "Customers",
    newName: "FullName"
);
```
**Tip:** If you rename your property in your model with the refactoring tools of Visual Studio (e.g. F2) EF Core is usually smart enough to recognise that you meant to rename the property. So you just need to check if the generated code is already correct.

#### Adding raw SQL
Sometimes you might want to execute specific operations that cannot be auto generated by EF Core.

If you want to rename and combine a `FirstName` and `LastName` property into a single `FullName` property, you need to replace the generated code

```csharp
migrationBuilder.DropColumn(
    name: "FirstName",
    table: "Customers"
);

migrationBuilder.DropColumn(
    name: "LastName",
    table: "Customers"
);

migrationBuilder.AddColumn<string>(
    name: "FullName",
    table: "Customers",
    nullable: true
);
```

with the following, in order to prevent unwanted data loss.

```csharp
migrationBuilder.AddColumn<string>(
    name: "FullName",
    table: "Customer",
    nullable: true
);

migrationBuilder.Sql(
@"
    UPDATE Customers
        SET FullName = FirstName + ' ' + LastName;
"
);

migrationBuilder.DropColumn(
    name: "FirstName",
    table: "Customers"
);

migrationBuilder.DropColumn(
    name: "LastName",
    table: "Customers"
);
```

#### Arbitrary changes via raw SQL
Raw SQL can also be used to manage database objects that EF Core is not aware of. You might add a migration without making any model change to generate an empty migration.

Then you can add the following to create a SQL Server stored procedure.

```csharp
migrationBuilder.Sql(
@"
    EXEC (
        'CREATE PROCEDURE sp_GetFullName
            @LastName nvarchar(50),
            @FirstName nvarchar(50)
        AS
            RETURN @LastName + @FirstName;'
    )
"
);
```

## Remove a migration
To remove the last migration, use this command.
 
 ```bash
 dotnet ef migrations remove
 ```

## Execute migrations
To create or update the database and schema, use this command.

```bash
dotnet ef database update
```

Please be aware that the recommended way to deploy migrations to a production database is by generating SQL scripts.

### Basic Usage
Create a SQL script from a blank database to the latest migration:

```bash
dotnet ef migrations script
```

### With From
Create a SQL script from the given migration to the latest migration:

```bash
dotnet ef migrations script AddNewTables
```

### With From and To
Create a SQL script from a specific migration to a specific migration:

```bash
dotnet ef migrations script AddNewTables AddAuditTable
```

You can use a ``from`` that is newer than the ``to`` in order to generate a rollback script.