EF Core - Database Schema Modifications

When you first create your database using EF Core, you create a migration with the initial schema definition. As you modify the schema, you generate new migrations, stacking them on top of the previous ones.

Note: I'll be using the dotnet ef CLI tool for managing migrations, and commands like dotnet ef database update to apply them.

Database Schema Modification Process

Here are the steps to follow when modifying your database schema:

1. Implement the schema change in your code.
2. Create a new migration.
3. Review the generated migration code for accuracy.
4. If there are issues, either break the changes into smaller migrations or modify the migration manually to address the problems.
5. Apply the migration either programmatically or with the dotnet ef tool.
6. Verify the changes in the database.

Adding a New Column

Let’s assume you need to add a new column to the Movies table.

First, modify your Movie class to include a new 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(500)]
    public string Description { get; set; }

    // New column added
    [Required]
    public string Genre { get; set; }
}

Next, create the migration:

dotnet ef migrations add AddGenreColumn

Check the generated migration code to ensure it correctly adds the new Genre column:

public partial class AddGenreColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Genre",
            table: "Movies",
            type: "nvarchar(255)",
            maxLength: 255,
            nullable: false,
            defaultValue: "");
    }

    // Down() not shown
}

If the migration code looks good, apply it:

dotnet ef database update

Now, you should see the new Genre column in the Movies table.

Fixing a Migration with Data Loss Risk

It's essential to thoroughly review generated migration code. The migration tool is not perfect, and mistakes can happen.

Let’s say you need to make the following changes to the Movies table:

1. Rename a column.
2. Add a new column.
3. Drop a column.

Start by modifying your Movie model:

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

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

    [Required]
    public int ReleaseYear { get; set; } // Renamed from YearOfRelease

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

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

    [Required]
    public decimal BoxOfficeRevenue { get; set; } // New column
}

Generate the migration:

dotnet ef migrations add UpdateMovieTable

Upon reviewing the generated code, you’ll likely see a warning that data loss is possible. The migration might mistakenly drop the wrong column or rename columns incorrectly.

If the migration looks incorrect, remove it:

dotnet ef migrations remove

Now, apply the changes in smaller steps to avoid data loss.

Preventing Data Loss by Minimizing Schema Changes per Migration

To avoid problems like the one above, it's a good idea to break up complex schema changes into smaller, more manageable migrations.

Small Migration 1

Let's start by focusing on renaming a column and adding a new one:

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

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

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

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

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

    [Required]
    public decimal BoxOfficeRevenue { get; set; } // New column
}

Generate the migration:

dotnet ef migrations add RenameAndAddColumn

Check that the migration is correct:

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

        migrationBuilder.AddColumn<decimal>(
            name: "BoxOfficeRevenue",
            table: "Movies",
            type: "decimal(18,2)",
            nullable: false,
            defaultValue: 0m);
    }

    // Down() not shown
}

Small Migration 2

Next, drop the RuntimeMinutes column:

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

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

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

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

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

    [Required]
    public decimal BoxOfficeRevenue { get; set; }
}

Generate the second migration:

dotnet ef migrations add DropRuntimeMinutesColumn

Review the migration:

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

    // Down() not shown
}

Apply the Pending Migrations

Once both migrations are verified, apply them:

dotnet ef database update

Both migrations will be applied, ensuring there’s no data loss because the changes were broken down into smaller, more manageable pieces.

Modifying Tables with Existing Data

While most schema changes can be applied safely to tables containing data, there are situations where an operation will fail.

Changing a Nullable Column to Non-Nullable

If you try to make a nullable column non-nullable, and the table already has null values in that column, you’ll face an error like:

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

To fix this, you can update existing null values to a default value before making the column non-nullable.

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

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

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

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

    [Required] // Making non-nullable
    [MaxLength(100)]
    public string Director { get; set; }

    [Required]
    public decimal BoxOfficeRevenue { get; set; }
}

Create the migration and modify it:

dotnet ef migrations add MakeDirectorNonNullable

Customize it to handle existing null values:

public partial class MakeDirectorNonNullable : 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: "");
    }

    // Down() not shown
}

Apply the migration:

dotnet ef database update

Reducing the Length of a String Column

If you try to reduce the length of a string column and the table contains data that exceeds the new length, you may encounter an error:

String or binary data would be truncated.

To handle this, you can truncate the data before applying the change.

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

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

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

    [Required]
    [MaxLength(30)] // Reduced length
    public string Description { get; set; }

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

    [Required]
    public decimal BoxOfficeRevenue { get; set; }
}

Create and customize the migration:

dotnet ef migrations add ReduceDescriptionLength

public partial class ReduceDescriptionLength : 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);
    }

    // Down() not shown
}

Apply the migration:

dotnet ef database update

By using this approach, you avoid truncating data unintentionally and can safely reduce the column length.