S8747 — Migrations should not narrow column types without converting existing data

This rule raises an issue when a database migration alters an existing column to a type that can hold a narrower range of values.

Why is this an issue?

Entity Framework Core migrations evolve the database schema over time, and the AlterColumn method changes the definition of an existing column. A migration narrows a column when it reduces the range of values the column can hold: a shorter maximum length for text, a smaller numeric type, or a lower precision or scale.

A schema change says nothing about the data already stored in the column. When existing values do not fit the new type, the database engine cannot keep them intact. Reducing a text column from nvarchar(max) to nvarchar(50) leaves no room for values longer than 50 characters, and converting a long column to int cannot represent values outside the int range.

Depending on the database engine, configuration, and type of narrowing, the migration either aborts with an error or silently truncates the affected values. For SQL Server, string length and integer range reductions abort (errors 2628 / 8115), while decimal precision and scale reductions can silently truncate. The migration only completes without converting the data first when every existing value already fits the narrower type, and relying on that is fragile: data that fits today may not fit tomorrow or in another environment.

What is the potential impact?

When the engine truncates, data that no longer fits is lost permanently and cannot be recovered. When the engine rejects the change instead, the migration aborts and halts the deployment, requiring manual intervention. On databases without transactional DDL (e.g. MySQL) or when transactions have been suppressed, the schema can additionally be left partially migrated.

Exceptions

This rule does not raise an issue when the migration explicitly converts the existing data to fit the new type before narrowing the column, for example with a migrationBuilder.Sql statement that updates the affected column.

How to fix it in Entity Framework Core

Before narrowing the column, convert the existing data so that every value fits the new type. Run an UPDATE statement through migrationBuilder.Sql to truncate, round, or otherwise transform the values, then call AlterColumn. Performing the conversion explicitly is what lets the migration succeed where it would otherwise abort, and it makes the data transformation visible and reviewable instead of leaving it to the database engine. This conversion is irreversible, so confirm that the transformed values are acceptable. When the narrowing is not actually required, keep the wider type instead.

Code examples

Noncompliant code example

public partial class ShortenStudentName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "Name",
            table: "Students",
            type: "nvarchar(50)", // Noncompliant: narrowing "Name" from "nvarchar(max)" truncates longer values
            maxLength: 50,
            nullable: false,
            oldClrType: typeof(string),
            oldType: "nvarchar(max)",
            oldNullable: true);
    }
}

Compliant solution

public partial class ShortenStudentName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Convert the existing data so that every value fits the narrower column
        migrationBuilder.Sql("UPDATE Students SET Name = LEFT(Name, 50) WHERE LEN(Name) > 50;");

        migrationBuilder.AlterColumn<string>(
            name: "Name",
            table: "Students",
            type: "nvarchar(50)",
            maxLength: 50,
            nullable: false,
            oldClrType: typeof(string),
            oldType: "nvarchar(max)",
            oldNullable: true);
    }
}

Resources

Documentation

• Microsoft Learn - ALTER TABLE (Transact-SQL)
• Microsoft Learn - Known Issues and Errors with Change Data Capture
• Microsoft Learn - Migrations Overview
• Microsoft Learn - Managing Migrations

Articles & blog posts

• Makolyte - EF Core — Database schema changes
• The Reformed Programmer - Handling Entity Framework Core database migrations in production
• Devart - SQL ALTER COLUMN Guide