Generating DDL with Entity Framework Core (Script-Migration)

Introduction

Entity Framework Core (EF Core) allows you to update the database using a procedure that doesn't directly involve SQL: "model changes → migrations → database updates". However, in a production environment, you might need to update the database using SQL directly, or you might want to confirm what SQL will be executed before making database changes. EF Core can generate DDL scripts to update the database based on the differences between migrations.

Execution Command

To generate SQL scripts for the differences between migrations, use the following commands.

dotnet ef migrations script [From] [To] [--context AppDbContext]

or

Script-Migration From [From] [To] [-Context コンテキスト名]

• From
  SQL is generated to apply migrations after From (From <).
  From is not included in the target migrations.

• To
  SQL is generated to apply migrations up to To (≤ To).
  To is included in the target migrations.
  If To is specified, From must also be specified.

• -Context コンテキスト名
  If you are using multiple DbContexts in a single project, you need to specify the target context.

Examples

Let's use the following migration history as an example.

Migrations/
├─ 20230516073612_Hoge_A.cs ... (A)
├─ 20230705015302_Hoge_B.cs ... (B)
├─ 20230705023114_Hoge_C.cs ... (C)
└─ 20230705075249_Hoge_D.cs ... (D)

Generate SQL to apply all migrations

dotnet ef migrations script

Generate (B) + (C) + (D)

Specify "after (A)".

dotnet ef migrations script Hoge_A

Generate (B) + (C)

Specify "after (A), up to (C)".

dotnet ef migrations script Hoge_A Hoge_C

To output to a file

Use the --output option.

dotnet ef migrations script --output update.sql

Reference

Script-Migration