tokenise by Rosvetic
Development How-to

Catch the migration that deletes your data before it ships

A short C# check reads the operations in an agent-generated EF Core migration and fails on drops, narrowing and silent defaults. Ten minutes to wire up.

10 min read

A smooth off-white drum with one side dissolving into scattered gold pixels

An agent renames Customer.Name to FullName, tightens the length to 50, adds a required Region property and generates a migration. The diff looks small. The tests pass, because they run against a fresh database with no rows in it. Then the migration runs against production and every customer name is gone.

EF Core doesn’t know that Name and FullName are the same column. It sees one property removed and another added, and it scaffolds a drop plus an add. The EF Core documentation says as much: apply that migration as-is and the data is lost, and the fix is to replace the pair with RenameColumn. It’s a mistake humans make too, but an agent makes it fluently, in a diff that looks tidy, and nobody reads the generated file because it’s generated.

A small check can read the migration for you. It loads the operations EF Core is about to run, flags the ones that destroy or rewrite data, and exits non-zero. It works the same whichever agent (or person) produced the migration, because it looks at the output and not at who wrote it. Total setup is one class and a few lines of Program.cs.

The scenario, reproduced

Start with a model on .NET 10 and SQLite (Microsoft.EntityFrameworkCore.Sqlite and .Design 10.0.14, plus the dotnet-ef tool):

public class Customer
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
    public string Name { get; set; } = "";
}

public class ShopContext : DbContext
{
    public DbSet<Customer> Customers => Set<Customer>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlite("Data Source=shop.db");
    protected override void OnModelCreating(ModelBuilder b) =>
        b.Entity<Customer>().Property(c => c.Name).HasMaxLength(200);
}

Create the first migration, then make the change the agent made:

public class Customer
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
    public string FullName { get; set; } = "";
    public string Region { get; set; } = "";
}
// and in OnModelCreating: .Property(c => c.FullName).HasMaxLength(50)

dotnet ef migrations add Initial
# edit the model
dotnet ef migrations add TidyCustomer

The second command finishes with a line that’s easy to scroll past:

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.

An agent running the command and reading the exit code sees success. The warning is on stdout, and nothing fails. The generated Up method looks like this:

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

migrationBuilder.AddColumn<string>(
    name: "FullName", table: "Customers", type: "TEXT",
    maxLength: 50, nullable: false, defaultValue: "");

migrationBuilder.AddColumn<string>(
    name: "Region", table: "Customers", type: "TEXT",
    nullable: false, defaultValue: "");

To see the damage, apply Initial, insert a row, then apply the new migration:

dotnet ef database update Initial
# insert: ('ada@example.com', 'Ada Lovelace')
dotnet ef database update

Reading the table back prints:

[(1, 'ada@example.com', '', '')]

The name is an empty string and the region is an empty string. Nothing errored. Both defaultValue: "" lines are EF Core being helpful: a required column needs a value for existing rows, so it supplies the CLR default.

Check operations, not text

The tempting version is a regex over the migration .cs file looking for DropColumn. It breaks on formatting, on raw SQL, and on migrations split across partial classes. EF Core already holds the answer in a structured form. A Migration exposes its UpOperations as a list of MigrationOperation objects, each with typed properties: table, column name, old column, new column, max length, nullability.

So the check instantiates the newest migration class by reflection and walks that list. Add this file to the project that holds the migrations:

using System.Reflection;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;

public record Finding(string Rule, string Target, string Why);

public static class MigrationAudit
{
    public static Migration Latest()
    {
        var type = typeof(MigrationAudit).Assembly.GetTypes()
            .Where(t => t.IsSubclassOf(typeof(Migration)) && !t.IsAbstract)
            .OrderBy(t => t.GetCustomAttribute<MigrationAttribute>()!.Id)
            .Last();
        return (Migration)Activator.CreateInstance(type)!;
    }

    public static List<Finding> Check(IReadOnlyList<MigrationOperation> ops)
    {
        var found = new List<Finding>();

        foreach (var op in ops)
        {
            switch (op)
            {
                case DropTableOperation t:
                    found.Add(new("drop-table", t.Name, "Every row in the table is lost."));
                    break;
                case DropColumnOperation c:
                    found.Add(new("drop-column", $"{c.Table}.{c.Name}", "The column's data is lost."));
                    break;
                case AlterColumnOperation a:
                    if (a.MaxLength is int now && a.OldColumn.MaxLength is int was && now < was)
                        found.Add(new("narrow-column", $"{a.Table}.{a.Name}",
                            $"Max length {was} to {now}: longer values are truncated or rejected."));
                    if (!a.IsNullable && a.OldColumn.IsNullable)
                        found.Add(new("null-to-not-null", $"{a.Table}.{a.Name}",
                            "Existing NULLs make the migration fail or get rewritten."));
                    if (a.ClrType != a.OldColumn.ClrType)
                        found.Add(new("type-change", $"{a.Table}.{a.Name}",
                            $"{a.OldColumn.ClrType.Name} to {a.ClrType.Name}: the conversion can lose data."));
                    break;
                case AddColumnOperation c when !c.IsNullable && c.DefaultValueSql is null && IsClrDefault(c.DefaultValue):
                    found.Add(new("implicit-default", $"{c.Table}.{c.Name}",
                        $"Existing rows get {Describe(c.DefaultValue)} because the column is required. Is that a real value?"));
                    break;
                case SqlOperation s:
                    found.Add(new("raw-sql", s.Sql.Split('\n')[0].Trim(), "Raw SQL is not analysed. Read it."));
                    break;
            }
        }

        // EF Core scaffolds a rename as a drop plus an add. Group the candidates per dropped column.
        foreach (var drop in ops.OfType<DropColumnOperation>())
        {
            var candidates = ops.OfType<AddColumnOperation>()
                .Where(a => a.Table == drop.Table)
                .Select(a => a.Name).ToList();
            if (candidates.Count > 0)
                found.Add(new("probable-rename", $"{drop.Table}.{drop.Name}",
                    $"Dropped while {string.Join(" and ", candidates)} {(candidates.Count == 1 ? "was" : "were")} added in the same table. If this is a rename, use RenameColumn so the data moves."));
        }

        return found;
    }

    static bool IsClrDefault(object? v) =>
        v is "" || (v is not null && v.GetType().IsValueType && v.Equals(Activator.CreateInstance(v.GetType())));

    static string Describe(object v) => v is string s ? $"\"{s}\"" : v.ToString()!;
}

Two decisions in there are worth defending. The implicit-default rule only fires when the default is the CLR default ("", 0, false, Guid.Empty). A deliberate defaultValue: "unknown" passes, because someone chose it. And the rename rule groups by dropped column instead of guessing which add pairs with it, because DropColumnOperation carries no type to match on. With one drop and two adds, the check lists both and leaves the call to the reader.

Replace Program.cs with the runner:

var migration = MigrationAudit.Latest();
var findings = MigrationAudit.Check(migration.UpOperations);

Console.WriteLine($"{migration.GetType().Name}: {migration.UpOperations.Count} operations");
foreach (var f in findings)
    Console.WriteLine($"  [{f.Rule}] {f.Target}: {f.Why}");

return findings.Count == 0 ? 0 : 1;

Run it with dotnet run against the agent’s migration:

TidyCustomer: 3 operations
  [drop-column] Customers.Name: The column's data is lost.
  [implicit-default] Customers.FullName: Existing rows get "" because the column is required. Is that a real value?
  [implicit-default] Customers.Region: Existing rows get "" because the column is required. Is that a real value?
  [probable-rename] Customers.Name: Dropped while FullName and Region were added in the same table. If this is a rename, use RenameColumn so the data moves.

The exit code is 1. Four findings. The drop and the probable rename describe the same hazard from two angles, and the rename message tells the agent what to do about it.

Feed the failure back

The check is only half the loop. Give the agent the command and the rule that goes with it. This works in any agent that can run a shell, so it goes in whichever instruction file yours reads:

After generating an EF Core migration, run `dotnet run` in the Shop project and fix
every finding before you commit. For a rename, edit the migration to use RenameColumn
and do not leave a DropColumn and AddColumn pair. For a required column on a table that
has data, supply a real defaultValue or backfill with a SQL step and say which you chose.
Do not delete findings by changing the check.

The last sentence matters. An agent whose task is “make this pass” will occasionally make it pass by editing the checker, so keep the check in a file the agent has to justify touching, and look at any diff that includes it.

Here’s the migration after the agent follows the instruction: Name becomes a RenameColumn, and Region gets a chosen default.

migrationBuilder.RenameColumn(name: "Name", table: "Customers", newName: "FullName");

migrationBuilder.AlterColumn<string>(
    name: "FullName", table: "Customers", type: "TEXT",
    maxLength: 200, nullable: false,
    oldClrType: typeof(string), oldType: "TEXT", oldMaxLength: 200);

migrationBuilder.AddColumn<string>(
    name: "Region", table: "Customers", type: "TEXT",
    nullable: false, defaultValue: "unknown");

The runner prints TidyCustomer: 3 operations, no findings, and exits 0. Applying it to the database that holds Ada’s row now gives:

[(1, 'ada@example.com', 'Ada Lovelace', 'unknown')]

The name survived and the region is something a person can search for later.

The narrowing case

That fixed migration kept the length at 200, so the 50 the model asked for is missing. Put the 50 back on the AlterColumn (maxLength: 50 with oldMaxLength: 200) and the check says:

TidyCustomer: 3 operations
  [narrow-column] Customers.FullName: Max length 200 to 50: longer values are truncated or rejected.

Whether that’s acceptable depends on the data. A person has to answer it, and the useful thing the check does is make sure someone is asked. The usual resolution is a query before the migration (SELECT COUNT(*) FROM Customers WHERE LENGTH(FullName) > 50) and a decision recorded in the pull request.

Acknowledging a deliberate drop

Sometimes the drop is the point: a column that’s been unused for six months and is finally being removed. The check shouldn’t block that forever. The cheapest honest mechanism is an allow file in the repository, one line per accepted finding:

20261102164500_RemoveLegacyFlag drop-column Customers.LegacyFlag

Load it in Program.cs and filter findings whose "<migration id> <rule> <target>" string appears in the file. Because the line names the migration id, an allowance for one migration can’t wave through the same drop in another. And because it’s a file in the diff, a reviewer sees the new line and asks the right question. That’s better than a suppression comment in the migration itself, which the agent can add without anyone noticing.

Where to run it

Locally, it takes seconds and needs no database, since it only reads the operation list. In CI, run it as a step after the build and before tests, on pull requests that touch the Migrations folder:

- name: Audit latest migration
  if: ${{ hashFiles('**/Migrations/*.cs') != '' }}
  run: dotnet run --project Shop

Checking only the newest migration is deliberate. Older ones have already shipped, and flagging them would make the check permanently red. If a branch adds several migrations, loop over all those whose id sorts after the one on main.

What it doesn’t catch

This is a check on EF Core’s operations, not on your data, so the limits are plain.

  • It can’t tell whether a drop is intended. It reports, and a person decides.
  • Raw SQL in migrationBuilder.Sql(...) is flagged but not analysed. An agent that writes UPDATE or DELETE statements gets a human read, which is the correct outcome.
  • It reads Up only. A Down method that loses data matters for rollbacks, so extend the runner to check DownOperations too if you rely on them.
  • Provider behaviour differs. On SQLite, an AlterColumn is executed as a table rebuild, and other providers may reject operations that SQLite accepts, so the production provider is the one to test against.
  • It can’t see index or constraint changes that lock a big table. A new unique index on a hot table is a different hazard, and worth a rule of your own if it bites you.

Don’t bother if the schema is owned by something else (a DBA-run script folder, or EnsureCreated in a throwaway service). The check only earns its place where EF Core migrations are the real path to production.

First ten minutes

Copy MigrationAudit.cs and the three-line runner into the migrations project, run dotnet run, and see what it says about the latest migration on the branch you have open. Then add the instruction paragraph to your agent’s instruction file and the CI step to the pipeline, in that order, and wait for the first finding to turn up.