Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 41 ยท Migrations

Seeding Data and Custom Migration SQL

Seed reference data with HasData and UseSeeding, run custom SQL in migrations with Sql(), backfill columns safely, and learn when each approach is the right tool.

Most applications need some rows to exist before anyone uses them: roles, countries, order statuses, an admin account for development. Migrations also sometimes need to move data, not just structure, for example splitting a FullName column into two. EF Core supports both: data seeding and custom SQL inside migrations.

The real EF Core and a database cannot run on this site, so the EF Core code is read-only and the runnable stand-ins model the behavior.

Model seeding with HasData

C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>().HasData(
        new Category { Id = 1, Name = "Books" },
        new Category { Id = 2, Name = "Games" },
        new Category { Id = 3, Name = "Music" });
}

HasData is part of the model, so migrations add turns it into InsertData, UpdateData and DeleteData calls:

C#
migrationBuilder.InsertData(
    table: "Categories",
    columns: new[] { "Id", "Name" },
    values: new object[,] { { 1, "Books" }, { 2, "Games" }, { 3, "Music" } });

Rules to remember:

  • Primary keys are required, even for generated keys, because EF Core must identify the row to update or delete in later migrations.
  • Seeded rows are managed by migrations. If you change Name = "Books" to "Literature" the next migration issues an UPDATE. If you remove the row from HasData, the next migration deletes it.
  • It suits small, stable reference data. It is a poor fit for large tables, values that depend on the environment (like an admin password) or data that users edit.
  • For relationships, seed the foreign key value (CategoryId = 1), not the navigation property.

UseSeeding and UseAsyncSeeding

EF Core 9 added code-based seeding that runs when you call Migrate, EnsureCreated or the database update command:

C#
options.UseSqlite("Data Source=shop.db")
       .UseSeeding((context, _) =>
       {
           if (!context.Set<Category>().Any())
           {
               context.Set<Category>().Add(new Category { Name = "Books" });
               context.SaveChanges();
           }
       })
       .UseAsyncSeeding(async (context, _, ct) =>
       {
           if (!await context.Set<Category>().AnyAsync(ct))
           {
               context.Set<Category>().Add(new Category { Name = "Books" });
               await context.SaveChangesAsync(ct);
           }
       });

This is ordinary code, so it can use generated keys, hash a development password, read a JSON file or check the environment. Always make it idempotent (check before inserting), because it runs on every migrate.

HasData UseSeeding
Lives in the model and migrations startup options
Keys must be provided can be generated
Changes tracked by migrations yes no
Good for small constant lookup rows dev data, environment-specific data

Custom SQL in a migration

Structure changes are generated, but you may add any SQL yourself in Up and Down:

C#
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>("FirstName", "Customers", nullable: true);
    migrationBuilder.AddColumn<string>("LastName", "Customers", nullable: true);

    migrationBuilder.Sql("""
        UPDATE Customers
        SET FirstName = substr(FullName, 1, instr(FullName, ' ') - 1),
            LastName  = substr(FullName, instr(FullName, ' ') + 1)
        WHERE instr(FullName, ' ') > 0
        """);

    migrationBuilder.DropColumn("FullName", "Customers");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>("FullName", "Customers", nullable: true);
    migrationBuilder.Sql("UPDATE Customers SET FullName = FirstName || ' ' || LastName");
    migrationBuilder.DropColumn("FirstName", "Customers");
    migrationBuilder.DropColumn("LastName", "Customers");
}

Notice the order: add new columns, copy data, then drop the old column. EF Core's generated code would have dropped FullName first and lost everything. Write the matching Down so the migration is reversible.

Other places SQL fits: creating views, stored procedures, triggers, full-text indexes and anything EF Core's model cannot express. Keep long SQL in embedded .sql files, load it with a helper and call migrationBuilder.Sql(text).

Raw SQL is database-specific. If you support several providers, branch on migrationBuilder.ActiveProvider.

Seeding options

HasData Constant rows in the model
UseSeeding Code at migrate time
Sql() Anything in a migration

Make seeding repeatable

1 / 2

Stand-in: HasData as insert, update and delete

This stand-in is not EF Core. It diffs the seed rows of the previous model with the current model, the way a migration does, and prints the generated operations.

C# Stand-in: diffing seed data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Stand-in: idempotent seeding and a data-moving migration

C# Stand-in: split a column safely

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

The one-word name "Cher" shows why migrations that move data need a thought for odd rows: a naive split would have crashed or produced empty data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why must HasData rows include the primary key?

    Seed rows are tracked by key across migrations, so even generated keys must be specified.
  2. 2. What happens if you remove a row from HasData and add a migration?

    HasData is part of the model; removing the row produces a DeleteData operation.
  3. 3. When is UseSeeding better than HasData?

    UseSeeding runs ordinary code at migrate time, so it can use generated keys, hashing and environment checks.
  4. 4. In a migration that splits FullName into FirstName and LastName, what is the safe order?

    Dropping first loses the data. Add, copy, then drop keeps it.

Summary

  • HasData seeds small constant rows through migrations and requires primary keys.
  • UseSeeding and UseAsyncSeeding run idempotent code for dynamic or environment-specific data.
  • migrationBuilder.Sql() runs any SQL, which is how you move data, create views and use database features.
  • Always add, copy, then drop, and write a matching Down.

Next: Scaffolding an Existing Database