EF Core – Database schema changes

Anytime you change the definition of the database – from renaming a column to creating a table – it’s referred to as a database schema change. With EF Core, you deal with database schema changes by using migrations.

When you first create the database with EF Core, you create a migration that contains the initial definition of the database. As you make schema changes, you add new migrations and apply them on top of the existing migrations.

In this article, I’ll show examples of going through the database schema change process in a few different scenarios, including error scenarios that require customizing the migration.

Note: I’ll be using the dotnet ef tool for handling migrations. I’ll use the command line – dotnet ef database update – for applying the migration. I’m working in a dev environment, so this is for simplicity and brevity.

Database schema change process

The following is the list of steps involved in the database schema change process:

• Make the schema change in the code.
• Create a new migration.
• Verify the correctness of the generated migration source code.
• If there are problems with the migration:
  • Split schema changes into smaller migrations -OR- customize the migration to correct the problems.
• Apply the migration programmatically (or with the dotnet ef tool).
• Verify correctness in the database.

Now I’ll show examples of doing this process in various scenarios.

Example – Adding a new table

Let’s say you want to add a new table called Shows.

First, add a new model class called Show:

using System.ComponentModel.DataAnnotations;

public class Show
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

	[Required]
	public int NumberOfEpisodes { get; set; }

	[Required]
	public int NumberOfSeasons { get; set; }
	
	[Required]
	public int FirstYear { get; set; }
	
	public int? LastYear { get; set; }
}
Code language: C# (cs)

Then add a DbSet<Show> property to your DbContext class:

public class StreamingServiceContext : DbContext
{
	private readonly string ConnectionString;
	public StreamingServiceContext(string connectionString)
	{
		ConnectionString = connectionString;
	}
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(ConnectionString);
	}
	public DbSet<Movie> Movies { get; set; }
	public DbSet<Show> Shows { get; set; }
}
Code language: C# (cs)

Create the migration:

dotnet ef migrations add Database_v1
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v1.cs and verify correctness:

public partial class Database_v1 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.CreateTable(
			name: "Shows",
			columns: table => new
			{
				Id = table.Column<int>(type: "int", nullable: false)
					.Annotation("SqlServer:Identity", "1, 1"),
				Name = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
				Description = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
				NumberOfEpisodes = table.Column<int>(type: "int", nullable: false),
				NumberOfSeasons = table.Column<int>(type: "int", nullable: false),
				FirstYear = table.Column<int>(type: "int", nullable: false),
				LastYear = table.Column<int>(type: "int", nullable: true)
			},
			constraints: table =>
			{
				table.PrimaryKey("PK_Shows", x => x.Id);
			});
	}
	//Down() not shown
}
Code language: C# (cs)

It looks correct, so apply the migration:

dotnot ef database update
Code language: PowerShell (powershell)

Now you’ll see the Shows table in the database. It’s a good idea to double-check the table definition in the database after the migration is applied.

Example – Incorrect migration leading to data loss and how to fix it

Always check the generated migration source code. This cannot be repeated enough. The migration generator is not perfect, as I will show below. Therefore, it’s always necessary to double-check the migration code.

Let’s say you have a Movies table and want to apply the following schema changes:

• Column name change.
• Add a new column.
• Drop a column.

Let’s see what happens if you try to apply all of these changes at the same time.

First, apply the schema changes to the Movie model:

public class Movie
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	public int YearOfRelease { get; set; } //changed name

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

	//dropped column
	//[Required]
	//public int RuntimeMinutes { get; set; }

	[MaxLength(100)]
	public string Director { get; set; }

	[Required]
	public decimal BoxOfficeRevenue { get; set; } //added new column
}
Code language: C# (cs)

Then generate the migration:

dotnet ef migrations add Database_v3
Code language: PowerShell (powershell)

You may notice the following warning (big red flag):

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.Code language: plaintext (plaintext)

Take a look at the generated migration source code in <timestamp>_Database_v3.cs, and pay close attention to the highlighted parts:

public partial class Database_v3 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.DropColumn(
			name: "ReleaseYear",
			table: "Movies");

		migrationBuilder.RenameColumn(
			name: "RuntimeMinutes",
			table: "Movies",
			newName: "YearOfRelease");

		migrationBuilder.AddColumn<decimal>(
			name: "BoxOfficeRevenue",
			table: "Movies",
			type: "decimal(18,2)",
			nullable: false,
			defaultValue: 0m);
	}
	
	//not showing Down()
}
Code language: C# (cs)

It dropped the wrong column – ReleaseYear instead of RuntimeMinutes – and it renamed the wrong column – RuntimeMinutes instead of ReleaseYear.

Since the migration is wrong, remove it:

dotnet ef migrations remove
Code language: PowerShell (powershell)

If your table had existing data in it, and you actually applied this migration, you’d have irreparable data loss. You’d end up with a column called YearOfRelease that has the RuntimeMinutes data in it.

This irreparable data loss may not be a big deal in a dev environment, but remember that you’ll eventually be applying the migration in a production environment. This is why you need to always double-check the generated migration code before applying it.

To prevent data loss, minimize the number of schema changes per migration

As shown above, generated migrations can be totally wrong and lead to data loss.

A simple solution is to create multiple, small migrations. Instead of trying to combine many schema changes into one migration, only include schema changes that can go together. You can figure out which changes can go together by trial and error.

The following example shows this approach.

Small migration 1

Let’s try to combine these two schema changes:

• Column name change.
• Add a new column.

First, make the changes in the Movie model:

public class Movie
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	public int YearOfRelease { get; set; } //changed name

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

	[Required]
	public int RuntimeMinutes { get; set; }

	[MaxLength(100)]
	public string Director { get; set; }

	[Required]
	public decimal BoxOfficeRevenue { get; set; } //added new column
}
Code language: C# (cs)

Then generate the migration:

dotnet ef migrations add Database_v3
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v3.cs:

public partial class Database_v3 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.RenameColumn(
			name: "ReleaseYear",
			table: "Movies",
			newName: "YearOfRelease");

		migrationBuilder.AddColumn<decimal>(
			name: "BoxOfficeRevenue",
			table: "Movies",
			type: "decimal(18,2)",
			nullable: false,
			defaultValue: 0m);
	}
//Down() not shown
}
Code language: C# (cs)

This time the migration source code is correct. It’s renaming the ReleaseYear column to YearOfRelease, and adding the new BoxOfficeRevenue column.

Small migration 2

The remaining schema change we need to do is Drop a column.

Apply this change in the Movie model:

public class Movie
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	public int YearOfRelease { get; set; }

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

	//dropped column
	//[Required]
	//public int RuntimeMinutes { get; set; }

	[MaxLength(100)]
	public string Director { get; set; }

	[Required]
	public decimal BoxOfficeRevenue { get; set; }
}
Code language: C# (cs)

Then generate a new migration:

dotnet ef migrations add Database_v4
Code language: PowerShell (powershell)

Take a look at the generated migration source code <timestamp>_Database_v4.cs:

public partial class Database_v4 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.DropColumn(
			name: "RuntimeMinutes",
			table: "Movies");
	}

	//Down() not shown
}
Code language: C# (cs)

This is correct. It’s dropping the RuntimeMinutes column.

Apply the two pending migrations

The two small migrations were created and verified for correctness. They are both pending migrations.

Take a look at the list of migrations:

dotnet ef migrations list
Code language: PowerShell (powershell)

20210314133726_Database_v0
20210315113855_Database_v1
20210316112804_Database_v2
20210316123742_Database_v3 (Pending)
20210316124316_Database_v4 (Pending)
Code language: plaintext (plaintext)

Now apply the two pending migrations:

dotnet ef database update
Code language: PowerShell (powershell)

Notice that this applied both migrations

Applying migration '20210316123742_Database_v3'.
Applying migration '20210316124316_Database_v4'.Code language: plaintext (plaintext)

Because the schema changes were done separately, the generated migrations were correct, and there was no data loss.

What happens when you change a table that has data?

There are many different schema changes you can do. Most of them can be applied to tables with existing data without problems. Don’t risk it though – always test your migrations in a dev environment against tables that have data in them.

With that said, there are some schema changes that will fail to apply to a table with data in them. When you run into this situation, you may be able to customize the migration to solve the problem. I’ll show two examples of this below.

Changing a nullable column to not nullable

When you try to change a nullable column to not allow nulls, and the table already has NULL values in that column, then the generated migration won’t handle it properly. You’ll run into this error:

Cannot insert the value NULL into column ‘Director’, table ‘StreamingService.dbo.Movies’; column does not allow nulls. UPDATE fails.

To solve this problem, you can customize the migration by updating nulls to a default value before it does the column change. I’ll show a full example of this scenario below.

Example of how to make a nullable not allow nulls

The Director column is currently nullable. To make it not nullable, add the [Required] attribute:

public class Movie
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	public int YearOfRelease { get; set; }

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

	[Required] //required = doesn't allow nulls
	[MaxLength(100)]
	public string Director { get; set; }

	[Required]
	public decimal BoxOfficeRevenue { get; set; }
}
Code language: C# (cs)

Create the migration for this change:

dotnet ef migrations add Database_v5
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v5.cs and customize it by executing an UPDATE statement with migrationBuilder.Sql():

public partial class Database_v5 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.Sql(@"UPDATE Movies SET Director = '' WHERE Director IS NULL");

		migrationBuilder.AlterColumn<string>(
			name: "Director",
			table: "Movies",
			type: "nvarchar(100)",
			maxLength: 100,
			nullable: false,
			defaultValue: "",
			oldClrType: typeof(string),
			oldType: "nvarchar(100)",
			oldMaxLength: 100,
			oldNullable: true);
	}

	//Down() not shown
}
Code language: C# (cs)

Apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

This was able to apply the migration without problems, because it replaced all nulls with empty strings, and then changed the column to not allow nulls.

Reducing the length of a string column

Let’s say you have existing strings that are 50 characters long, and you want to change the maximum length of this string column to 40 characters. When you try to apply this schema change, you’ll run into the following error:

String or binary data would be truncated.

First, make sure you’re OK with truncating the existing data in the table.

You can solve this problem by customizing the migration to truncate the string column before it does the column change.

Disclaimer: Doing this will result in data loss because you’ll be intentionally truncating the string column. Don’t do this if you don’t want to lose data.

Example of reducing the length of a string column

First, change the [MaxLength] attribute for the Description column:

public class Movie
{
	[Key]
	public int Id { get; set; }

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	public int YearOfRelease { get; set; }

	[Required]
	[MaxLength(30)] //reduced from 500 to 30
	public string Description { get; set; }

	[Required]
	[MaxLength(100)]
	public string Director { get; set; }

	[Required]
	public decimal BoxOfficeRevenue { get; set; }
}
Code language: C# (cs)

Then create a new migration:

dotnet ef migrations add Database_v6

Now take a look at the generated migration source code in <timestamp>_Database_v6.cs and customize it by executing an UPDATE statement with migrationBuilder.Sql():

public partial class Database_v6 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.Sql(@"UPDATE Movies SET Description = LEFT(Description, 30) WHERE LEN(Description) > 30");

		migrationBuilder.AlterColumn<string>(
			name: "Description",
			table: "Movies",
			type: "nvarchar(30)",
			maxLength: 30,
			nullable: false,
			oldClrType: typeof(string),
			oldType: "nvarchar(500)",
			oldMaxLength: 500);
	}

	//Down() not shown
}
Code language: C# (cs)

Apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

This successfully applied the migration by first truncating the string column to the reduced length, then it changed the length of the column.