EF Core Migrations: Change a Database from C# – Data Access | Extra 10
Introduction
In Module 9 the SMS used EF Core database-first: the SQL scripts in database/ build SchoolDb, and EF Core
reads what they built. Many teams work the other way round.
With code-first, the C# classes are the design, and EF Core migrations write the SQL that changes the database to match. This lesson builds a small library database that way, from the first table to a production release.
By the end of this lesson, you will be able to:
- Create a database from C# classes with
dotnet ef migrations addanddatabase update. - Read a migration before running it, and spot changes that lose data.
- Write a data migration by hand, with an
Upand aDownthat both keep the data. - Roll back to an earlier migration, and remove one that nobody else has.
- Ship migrations to production with an idempotent script or a migration bundle.
Why this matters
A database changes all through a project's life: new columns, renamed columns, new tables, data moved from one place to another. Every developer's database, the test server, and production must all get the same changes, in the same order, exactly once.
Migrations are EF Core's answer, and you will meet them in most .NET jobs. They are also easy to misuse: one command can drop a column full of data, and the tool only prints a warning.
This lesson shows both sides on a real database: what migrations do well, and where you must read and fix them.
The concept
Think of a school building's plans. Every change is a numbered work order, such as "build the library room" or "rename room B", and a register on the wall lists the orders already done.
A second building gets the same orders in the same order. A migration is a work order, and EF Core's register is
a table called __EFMigrationsHistory.
Example: the three files of a migration
dotnet ef migrations add InitialCreate compares your classes with the last known model and writes:
| File | What it holds |
|---|---|
20261012145935_InitialCreate.cs | Up (make the change) and Down (undo it), as C# calls |
20261012145935_InitialCreate.Designer.cs | The model at that moment, for EF Core's own use |
LibraryContextModelSnapshot.cs | The latest model; the next migration is compared with it |
The number at the front is the date and time. It keeps migrations in the order they were made.
Example: the commands
dotnet ef migrations add AddPublishedYear # compare the classes with the snapshot; write a migration
dotnet ef migrations script # show the SQL, without running it
dotnet ef database update # run every migration the database does not have yet
dotnet ef database update RenameAuthor # go back (or forward) to exactly this migration
dotnet ef migrations remove # delete the last migration, if it was never applied
dotnet ef migrations has-pending-model-changes # do the classes and the migrations agree?
dotnet ef is the local tool from Lesson 59, listed in dotnet-tools.json. The project needs the package
Microsoft.EntityFrameworkCore.Design.
Apply it to the SMS
The SMS itself stays database-first: its numbered scripts are the single source of truth, and two sources would fight. So this lesson builds a separate database, LibraryDb, for the library module you have practised in every homework.
Step 1: the classes and the context
using Microsoft.EntityFrameworkCore;
namespace LibraryDb;
// Extra 10: the DbContext, as in Lesson 59. "dotnet ef" creates it with the parameterless constructor, so the
// connection string comes from OnConfiguring: the LIBRARYDB_CONNECTION environment variable, or the local server.
public class LibraryContext : DbContext
{
public DbSet<Book> Books => Set<Book>();
public DbSet<Loan> Loans => Set<Loan>();
public DbSet<Member> Members => Set<Member>();
protected override void OnConfiguring(DbContextOptionsBuilder options) =>
options.UseSqlServer(Environment.GetEnvironmentVariable("LIBRARYDB_CONNECTION")
?? "Server=localhost;Database=LibraryDb;Trusted_Connection=True;TrustServerCertificate=True;");
protected override void OnModelCreating(ModelBuilder model)
{
model.Entity<Book>(book =>
{
book.Property(b => b.Isbn).HasMaxLength(13).IsUnicode(false);
book.HasIndex(b => b.Isbn).IsUnique();
book.Property(b => b.Title).HasMaxLength(200);
book.Property(b => b.AuthorName).HasMaxLength(100);
});
model.Entity<Loan>(loan =>
{
// EF Core's default here is Cascade: deleting a book would delete its loan history. Refuse instead.
loan.HasOne(l => l.Book).WithMany(b => b.Loans).OnDelete(DeleteBehavior.Restrict);
loan.HasOne(l => l.Member).WithMany(m => m.Loans).OnDelete(DeleteBehavior.Restrict);
});
model.Entity<Member>(member =>
{
member.Property(m => m.Name).HasMaxLength(100);
});
}
}
The classes are shown here as they are at the end of the lesson, after four migrations. The first version had
Author instead of AuthorName, no PublishedYear, and a BorrowerName typed on every loan instead of a
Member.
Show extras/e10-ef-migrations/LibraryDb/Model.cs
namespace LibraryDb;
// Extra 10: the library's classes. With code-first, these classes are the design of the database:
// a migration compares them with the last snapshot and writes the SQL changes.
public class Book
{
public int Id { get; set; }
public string Isbn { get; set; } = "";
public string Title { get; set; } = "";
public string AuthorName { get; set; } = ""; // migration 3: renamed from Author
public int? PublishedYear { get; set; } // migration 2: a new column, empty for the books already there
public List<Loan> Loans { get; set; } = [];
}
public class Loan
{
public int Id { get; set; }
public int BookId { get; set; }
public Book? Book { get; set; }
public int MemberId { get; set; } // migration 4: a member, instead of a name typed on every loan
public Member? Member { get; set; }
public DateOnly IssuedOn { get; set; }
public DateOnly DueOn { get; set; }
public DateOnly? ReturnedOn { get; set; }
}
// Migration 4: each borrower once, so "Aarav Sharma" and "Aarav Sharma" can no longer be two people.
public class Member
{
public int Id { get; set; }
public string Name { get; set; } = "";
public List<Loan> Loans { get; set; } = [];
}
Example: migration 1, and the first review
The first InitialCreate had a surprise in it. EF Core's default for the loan's book was:
table.ForeignKey(
name: "FK_Loans_Books_BookId",
column: x => x.BookId,
principalTable: "Books",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
Cascade means deleting a book deletes all its loans: the library's history, gone without a question. The
migration had not been applied, so we ran dotnet ef migrations remove, added OnDelete(DeleteBehavior.Restrict)
to the context, and created it again.
dotnet ef migrations script then showed the SQL, ending in ON DELETE NO ACTION. dotnet ef database update
created the database itself, the tables, and the register:
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20261012145935_InitialCreate'.
Done.
MigrationId ProductVersion
20261012145935_InitialCreate 10.0.12
Example: migration 2, a new column
public int? PublishedYear on Book became AddColumn in Up and DropColumn in Down. It is nullable, so the
two books already in the table simply have no year yet.
Example: migration 3, a rename, safe and unsafe
Renaming Author to AuthorName gave exactly what we wanted:
migrationBuilder.RenameColumn(
name: "Author",
table: "Books",
newName: "AuthorName");
Then we tried the same rename and a longer maximum length (150) in one step. EF Core could no longer tell it was the same column:
An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
migrationBuilder.DropColumn(
name: "Author",
table: "Books");
migrationBuilder.AddColumn<string>(
name: "AuthorName",
table: "Books",
type: "nvarchar(150)",
maxLength: 150,
nullable: false,
defaultValue: "");
Every author would have become an empty string. The lesson: one kind of change per migration, and read every migration before you apply it.
Example: migration 4, moving data
Typing the borrower's name on every loan lets "Aarav Sharma" and "Aarav Sharma" become two people. So loans get a
Member instead. EF Core wrote this, in this order:
DropColumn BorrowerName <- every name, gone
AddColumn MemberId = 0
CreateTable Members
AddForeignKey Loans.MemberId -> Members.Id
We applied it on purpose, to see what happens:
Applying migration '20261012150213_AddMembers'.
Microsoft.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY
constraint "FK_Loans_Members_MemberId". The conflict occurred in database "LibraryDb", table "dbo.Members", column 'Id'.
Every loan had MemberId = 0, and no member has Id 0. EF Core runs each migration in a transaction, so nothing was
left half-done: BorrowerName was still there, and Members did not exist. Then we wrote the migration by hand:
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace LibraryDb.Migrations
{
/// <inheritdoc />
public partial class AddMembers : Migration
{
// Written by hand (Extra 10). EF Core's own version dropped BorrowerName first, losing every name, and then
// failed: MemberId = 0 matched no member. This order keeps the names: make the members from them, point each
// loan at its member, and only then drop the old column.
// The SQL written by hand is inside EXEC(N'...'): SQL Server then compiles it only if it runs. In an idempotent
// script (--idempotent), a second run would otherwise fail on BorrowerName, which by then no longer exists.
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Members",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Members", x => x.Id);
});
// 1. One member for each different name already typed on a loan.
migrationBuilder.Sql("EXEC(N'INSERT INTO Members (Name) SELECT DISTINCT BorrowerName FROM Loans;');");
// 2. The new column, then each loan's member, found by name.
migrationBuilder.AddColumn<int>(
name: "MemberId",
table: "Loans",
type: "int",
nullable: false,
defaultValue: 0);
migrationBuilder.Sql("EXEC(N'UPDATE l SET MemberId = m.Id FROM Loans AS l JOIN Members AS m ON m.Name = l.BorrowerName;');");
// 3. Now the old column can go, and the foreign key can be checked.
migrationBuilder.DropColumn(
name: "BorrowerName",
table: "Loans");
migrationBuilder.CreateIndex(
name: "IX_Loans_MemberId",
table: "Loans",
column: "MemberId");
migrationBuilder.AddForeignKey(
name: "FK_Loans_Members_MemberId",
table: "Loans",
column: "MemberId",
principalTable: "Members",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
}
// Down undoes Up, in reverse, and also keeps the names: back from Members into BorrowerName.
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<string>(
name: "BorrowerName",
table: "Loans",
type: "nvarchar(100)",
maxLength: 100,
nullable: false,
defaultValue: "");
migrationBuilder.Sql("EXEC(N'UPDATE l SET BorrowerName = m.Name FROM Loans AS l JOIN Members AS m ON m.Id = l.MemberId;');");
migrationBuilder.DropForeignKey(
name: "FK_Loans_Members_MemberId",
table: "Loans");
migrationBuilder.DropIndex(
name: "IX_Loans_MemberId",
table: "Loans");
migrationBuilder.DropColumn(
name: "MemberId",
table: "Loans");
migrationBuilder.DropTable(
name: "Members");
}
}
}
Id Name Id BookId MemberId
1 Aarav Sharma 1 1 1
2 Diya Patel 2 2 2
3 2 1
Two members from three loans, and each loan points at the right one.
Example: going back, and forward again
dotnet ef database update RenameAuthor ran AddMembers.Down. The names came back from Members into
BorrowerName, and the table was dropped:
Reverting migration '20261012150213_AddMembers'.
Done.
Aarav Sharma, Diya Patel, Aarav Sharma (the three loans' BorrowerName)
dotnet ef database update applied it again, and the program listed the same three loans. A Down that keeps the
data is what makes a rollback safe on a bad release day.
Step 2: shipping to production
Developers run database update on their own machines. Production is different: there, a person or a release
pipeline should run a reviewed change. Two tools make that possible.
An idempotent script is plain SQL that checks the register before every step, so it can run on a database at any migration, any number of times:
IF NOT EXISTS (
SELECT * FROM [__EFMigrationsHistory]
WHERE [MigrationId] = N'20261012150005_AddPublishedYear'
)
BEGIN
ALTER TABLE [Books] ADD [PublishedYear] int NULL;
END;
Our first version failed on its second run:
Msg 207, Level 16, State 1, Line 20
Invalid column name 'BorrowerName'.
SQL Server compiles a whole batch before running any of it. On the second run BorrowerName no longer existed, so
our hand-written INSERT failed to compile, even inside an IF that would skip it. Wrapping that SQL in
EXEC(N'...') fixed it: now it is compiled only if it runs.
After the fix the script ran twice on an empty database (exit code 0 both times, 4 migrations). It also upgraded a
database stopped at RenameAuthor with three real loans: "Meera Iyer" and "Rohan Das" became members.
A migration bundle is one program that contains the migrations. The pipeline runs it with a connection string:
dotnet ef migrations bundle -o efbundle.exe
efbundle.exe --connection "Server=...;Database=LibraryDb;..."
Applying migration '20261012145935_InitialCreate'.
Applying migration '20261012150005_AddPublishedYear'.
Applying migration '20261012150120_RenameAuthor'.
Applying migration '20261012150213_AddMembers'.
Done.
No migrations were applied. The database is already up to date.
Done.
Example: why not migrate when the app starts
db.Database.Migrate() in Program.cs looks convenient. Since EF Core 9 it even takes the lock you saw in the
output, so two servers starting together do not both apply a migration.
It is still a poor fit for production. The app's database login then needs permission to change tables, a slow migration delays every start, and a failed migration means the app does not start at all. A script or a bundle, run once by the release, avoids all three.
Example: SQL scripts or migrations?
| Numbered SQL scripts (the SMS) | EF Core migrations (LibraryDb) | |
|---|---|---|
| The design lives in | SQL files | C# classes |
| Written by | People, in SQL | EF Core, then reviewed and fixed by people |
| Best when | The database is shared, older than the app, or owned by a database team | One app owns its database |
| Data moves | Plain SQL, as in every SMS script | migrationBuilder.Sql, written by hand |
| Production | Run the scripts in order | An idempotent script or a bundle |
Neither is better everywhere. What matters is one source of truth: never let both change the same database.
Common mistakes
- Applying a migration without reading it. A rename can become a drop. Fix: read
UpandDown, andmigrations script, every time. - Several changes in one migration. EF Core may misread them, as with rename plus widen. Fix: one kind of change per migration.
- Trusting the generated order for data changes. The data was dropped before it was moved. Fix: write data
migrations by hand, and test
Downtoo. - Editing or removing a migration someone else applied. Their databases no longer match. Fix: remove only your own unapplied migrations; otherwise add a new one.
- Plain SQL in a migration used by an idempotent script. It fails to compile on the second run. Fix:
EXEC(N'...'). - Two sources of truth. Scripts and migrations both changing one database. Fix: choose one.
In real projects
Two developers added a migration on the same day, each on their own branch. Both branches passed their tests.
After merging, the next developer's migrations add produced a strange migration that re-created a column that
already existed.
Both migrations had changed the snapshot file, and the merge had kept only one version of it. The model and the migrations no longer agreed.
The team added dotnet ef migrations has-pending-model-changes to the build. It fails when the classes and the
migrations disagree, so the problem now shows up in the pull request, not a week later.
Best practices
- Read every migration, and its SQL, before applying it; fix the defaults you disagree with, such as cascade deletes.
- Make one kind of change per migration, with a clear name.
- Write data migrations by hand, in an order that never loses data, with a
Downthat keeps it too. - Ship to production with an idempotent script or a bundle, run by the release, not by the app at startup.
- Check
has-pending-model-changesin the build. - Keep one source of truth for each database.
Recap
- Migrations turn changes in C# classes into ordered
Up/Downsteps, recorded in__EFMigrationsHistory. - Our review caught a cascade delete, a rename that would drop data, and a generated data migration that failed.
AddMemberswas rewritten by hand: names became members on the way up, and came back on the way down.- The idempotent script needed
EXEC(N'...')for hand-written SQL; then it ran twice and upgraded an old database. - The bundle applied four migrations, then reported the database up to date.
- The SMS stays database-first; LibraryDb shows code-first. Choose one per database.
Homework
Library module task: Members want to reserve books that are out on loan.
- Add a
Reservationclass (book, member, reserved on) and itsDbSet. - Allow one reservation per member per book with a unique index, and no cascading deletes.
- Create and apply the
AddReservationsmigration. Try to insert the same reservation twice. - Roll back to
AddMembers, remove the migration, remove the class, and check that the model and migrations agree.
A correct result looks like:
Applying migration '20261012150719_AddReservations'.
Cannot insert duplicate key row in object 'dbo.Reservations' with unique index 'IX_Reservations_BookId_MemberId'. The duplicate key value is (2, 1).
Reverting migration '20261012150719_AddReservations'.
Reverting the model snapshot.
No changes have been made to the model since the last migration.
Show the solution
namespace LibraryDb;
// Library homework, Extra 10: a member reserves a book that is out on loan. One reservation per member per book.
public class Reservation
{
public int Id { get; set; }
public int BookId { get; set; }
public Book? Book { get; set; }
public int MemberId { get; set; }
public Member? Member { get; set; }
public DateOnly ReservedOn { get; set; }
}
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace LibraryDb.Migrations
{
/// <inheritdoc />
public partial class AddReservations : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Reservations",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
BookId = table.Column<int>(type: "int", nullable: false),
MemberId = table.Column<int>(type: "int", nullable: false),
ReservedOn = table.Column<DateOnly>(type: "date", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Reservations", x => x.Id);
table.ForeignKey(
name: "FK_Reservations_Books_BookId",
column: x => x.BookId,
principalTable: "Books",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
table.ForeignKey(
name: "FK_Reservations_Members_MemberId",
column: x => x.MemberId,
principalTable: "Members",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
});
migrationBuilder.CreateIndex(
name: "IX_Reservations_BookId_MemberId",
table: "Reservations",
columns: new[] { "BookId", "MemberId" },
unique: true);
migrationBuilder.CreateIndex(
name: "IX_Reservations_MemberId",
table: "Reservations",
column: "MemberId");
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Reservations");
}
}
}
The context lines and every command are in library-assignment/e10-reservations/README.md. Before we removed the
class, has-pending-model-changes said "Changes have been made to the model since the last migration": a removed
migration with the class still in place is exactly the mismatch the build check catches.
Quiz
Where does EF Core record which migrations a database already has?
Show answer
Answer: B. In the __EFMigrationsHistory table database update compares that table with the migrations in the project.
Renaming a property and changing its length in one step produced what?
Show answer
Answer: C. DropColumn and AddColumn, with a data loss warning One kind of change per migration lets EF Core see a rename as a rename.
Why did the generated AddMembers migration fail?
Show answer
Answer: B. Every loan got MemberId 0, which matched no member, so the foreign key failed It had also dropped BorrowerName first. The transaction rolled everything back.
Why was the hand-written SQL wrapped in EXEC(N'...')?
Show answer
Answer: B. So SQL Server compiles it only if it runs, which keeps the idempotent script working on a second run Without it, the second run failed: Invalid column name 'BorrowerName'.
Why is Database.Migrate() at startup a poor fit for production?
Show answer
Answer: B. The app login needs permission to change tables, starts get slow, and a failed migration stops the app A script or bundle, run once by the release, avoids all three.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.
Fresher (0-2 yrs)
F What is an EF Core migration? concept
A C# class, written by dotnet ef migrations add, with an Up method that changes the database to match the
model and a Down method that undoes it. EF Core records applied migrations in the __EFMigrationsHistory
table, so database update runs only the ones a database does not have yet.
F What is the difference between database-first and code-first? concept
Database-first: the database is designed in SQL, and EF Core reads it. Code-first: the C# classes are the design, and migrations create and change the database. Database-first suits shared or older databases; code-first suits a database that one application owns.
F Which dotnet ef commands do you use day to day with migrations? coding
migrations add <Name> to create one, migrations script to see its SQL, database update to apply all
pending ones, database update <Name> to go back or forward to one, migrations remove to delete the last
unapplied one, and migrations list to see them.
Mid (3-5 yrs)
M Why must you read a migration before applying it? Give examples. scenario
EF Core guesses from the model difference, and some guesses lose data. A rename combined with another change
becomes DropColumn plus AddColumn. A new required foreign key gets default 0 that matches no row. Defaults such
as cascade delete may not be what the business wants. Read Up, Down, and the SQL, and fix them.
M How do you write a data migration, for example moving a text column into a new table? coding
Edit the generated migration: create the new table, copy the data with migrationBuilder.Sql, add and fill
the new key column, and only then drop the old column and add the foreign key. Write Down to move the data
back. Wrap hand-written SQL in EXEC(N'...') so idempotent scripts still compile, and test both directions.
M How do you apply migrations to a production database? concept
Not from the app at startup. Generate an idempotent script (migrations script --idempotent) for a reviewer
or DBA to run, or build a migration bundle (migrations bundle) that the release pipeline runs with the
production connection string. Both skip migrations already in __EFMigrationsHistory.
Senior (6-9 yrs)
S Should an application call Database.Migrate() when it starts? design
Usually not in production. EF Core 9 and later take a lock so two instances do not both migrate, but the app's login then needs permission to change the schema, a long migration delays start-up, and a failed migration stops the app from starting. Run migrations once in the release, with a script or bundle, under a separate login.
How to answer: Mention the lock, then permissions, start-up time, failure handling, and the alternative.
S How do you keep migrations healthy in a team where several developers change the model? scenario
One kind of change per migration, reviewed in pull requests with its SQL. Never edit or remove a migration
others have applied; add a new one. Resolve snapshot merge conflicts carefully and run
migrations has-pending-model-changes in the build so a model that disagrees with the migrations fails CI.
Test migrations, including Down, against a copy of real data before release.
How to answer: Cover small migrations, review, never rewriting applied ones, snapshot conflicts, CI checks, and testing.
Architect / Lead (10+ yrs)
A How do you change a large production database without downtime? design
Use expand and contract: first add new structures without breaking the old ones (new nullable columns or tables, both written by the app), backfill data in batches, switch reads to the new structure in a release, and only later remove the old columns. Keep every step backward compatible with the running version, avoid long blocking operations (online index builds, batched updates), and have a tested rollback for each step.
How to answer: Explain expand and contract, compatibility between versions, batching, locking, and rollback per step.
Resources
- Code for this lesson: git tag
extra-10-ef-migrations(code repository coming soon) - Files:
extras/e10-ef-migrations/LibraryDb/LibraryContext.cs,extras/e10-ef-migrations/LibraryDb/Model.cs,extras/e10-ef-migrations/LibraryDb/Migrations/20261012150213_AddMembers.cs,extras/e10-ef-migrations/library-migrations.sql,library-assignment/e10-reservations/README.md - Video: coming soon
What's next
The last Web API extra is about how a team works on all this code together: Git branches, pull requests, and reviews. Until it is published, continue with the capstone: Lesson 97: From one school to 1,000
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.