02 - Managing Schemas

Managing Schemas

Over time data model (entities) change and get out of sync with database. Simplest solution is to drop database and recreate in from zero. In real life - this is not usually possible; data loss is not acceptable.

EF Core provides different ways for updating database – called Migrations.

Migration

Migration include command line tools and/or code APIs:

• Create a migration. Generate code that can update the database to sync it with a set of model changes.
• Update the database. Apply pending migrations to update the database schema.
• Customize migration code. Sometimes the generated code needs to be modified or supplemented.
• Remove a migration. Delete the generated code.
• Revert a migration. Undo the database changes.
• Generate SQL scripts. You might need a script to update a production database or to troubleshoot migration code.
• Apply migrations at runtime. When design-time updates and running scripts aren't the best options, call the Migrate() method.

Install/Check tooling

• dotnet tool install --global dotnet-ef
• dotnet tool update --global dotnet-ef
• dotnet tool list –g
• dotnet --version
• dotnet --info

Add Migration

Add new/initial migration
dotnet ef migrations add InitialCreate

If needed, add options to specify startup project, project where dbcontext is and dbcontext itself (if there are several).
--project DAL --startup-project WebApp --context AppDbContext

00000000000000_InitialCreate.cs
The main migrations file. Contains the operations necessary to apply the migration (in Up()) and to revert it (in Down()).

00000000000000_InitialCreate.Designer.cs
The migrations metadata file. Contains information used by EF.

MyContextModelSnapshot.cs
A snapshot of your current model. Used to determine what changed when adding the next migration.

The timestamp in the filename helps keep them ordered chronologically

Apply Migration

Update the database
dotnet ef database update

Customize Migrations

Sometimes migration would cause data loss – some SQL operations are needed, before changing DB structure too much

The migration scaffolding process warns when an operation might result in data loss (like dropping a column). If you see that warning, be especially sure to review the migrations code for accuracy

migrationBuilder.AddColumn<string>(
    name: "Name",
    table: "Customer",
    nullable: true
);
// CUSTOM SQL
migrationBuilder.Sql(
    @" UPDATE Customer SET Name = FirstName + ' ' + LastName;"
);
migrationBuilder.DropColumn(
    name: "FirstName",
    table: "Customer"
);
migrationBuilder.DropColumn(
    name: "LastName",
    table: "Customer"
);

Remove/Revert Migration

Sometimes you add a migration and realize you need to make additional changes to your EF Core model before applying it. To remove the last migration, use this command.

dotnet ef migrations remove

If you already applied a migration (or several migrations) to the database but need to revert it, you can use the same command to apply migrations, but specify the name of the migration you want to roll back to. (Migrations after that one will be reverted)

dotnet ef database update LastGoodMigration

Generate SQL

When debugging your migrations or deploying them to a production database, it's useful to generate a SQL script. The script can then be further reviewed for accuracy and tuned to fit the needs of a production database.

dotnet ef migrations script

Options - from, to

• The from migration should be the last migration applied to the database before running the script. If no migrations have been applied, specify 0 (this is the default).
• The to migration is the last migration that will be applied to the database after running the script. This defaults to the last migration in your project.

Apply migrations at runtime

Some apps may want to apply migrations at runtime during startup or first run. Do this using the Migrate() method.

• myDbContext.Database.Migrate();
• All unapplied migrations will be added one-by-one in sequence.

Drop the DB

If you just need to drop the database

dotnet ef database drop

If you moved your DbContext into different project you need to specify on every EF command which project is startup-project and where is DbContext

dotnet ef .... --project DAL --startup-project WebApp

Create and Drop APIs

The EnsureCreated and EnsureDeleted methods provide a lightweight alternative to Migrations for managing the database schema. These methods are useful in scenarios when the data is transient and can be dropped when the schema changes.

EnsureCreated and Migrations don't work well together. If you're using Migrations, don't use EnsureCreated to initialize the schema

The EnsureDeleted method will drop the database if it exists. dbContext.Database.EnsureDeleted();

EnsureCreated will create the database if it doesn't exist and initialize the database schema. If any tables exist (including tables for another DbContext class), the schema won't be initialized. dbContext.Database.EnsureCreated();

Async is also available!

Reverse engineering

Reverse engineering is the process of scaffolding entity type classes and a DbContext class based on a database schema.

dotnet ef dbcontext scaffold
"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ContactDb”
Microsoft.EntityFrameworkCore.SqlServer
--table Persons --table Contacts --table ContactTypes
--data-annotations
--context AppDbContext
--context-dir Data --output-dir Models

String escaping according to shell you are using!