In this tutorial, we look at what is ef core migrations are and how to create migrations. We will build an ef core migrations example app to show you how to create migrations. The app will create two models. We then use the add-migration command to create the migration. We can revert or remove the migration using the remove-migration. Once we create the migration we can push it to the database using the update-database command. Or we can also create the SQL script using the script-migration Once we have the script we can use it to update the production database. We also show you how to revert the migration from the database.
Source Code:
The source code of this project available in GitHub.
Table of Contents
What is EF Core Migrations
In EF core we create or update data models in our code. We call this approach the Code First approach. As our app grows, we are going to make a lot of changes to our model. This will result in our database going out of sync with the model.
Hence we need to update the database as we make changes to the model. But making changes manually is error-prone. We can also create a fresh database from the model, but it will result in loss of data.
The EF Core migrations make it easier to push the changes to the database and also preserve the existing data in the database. It provides commands like add-migration, remove-migration to create & remove migrations. Once migrations are generated, we update the database using the update-database. We can also generate the migration script and execute it in the production server.
How to Create Ef Core Migrations
Let us create a new console application to see how migrations work in entity framework core. You can refer to the Tutorial Entity Framework Core Console Application to learn how to create the Console application.
First, We will create a model and context class. Then we use the Migrations commands to create migrations and then later update the database.
Create a New Console Application
Open Visual Studio 2017 and create a new .NET Core Console Application. Name the App as EFCoreMigration. You can also use the Visual Studio 2019
Install the Microsof.Entity FrameworkCore.SqlServer package, which in turn installs the Entity Framework Core package
1 2 3 | Install-package Microsoft.EntityFrameworkCore.SqlServer |
Models for Migration
Create the Models folder and create two class Product.cs and Vendor.cs as shown below
1 2 3 4 5 6 7 8 9 10 | namespace EFCoreMigration.Models { public class Product { public int Id { get; set; } public string Name { get; set; } } } |
1 2 3 4 5 6 7 8 9 10 | namespace EFCoreMigration.Models { public class Vendor { public int Id { get; set; } public string Name { get; set; } } } |
Context
Under the models folder create the context class EFContext, which inherits from DBContext as shown below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | using Microsoft.EntityFrameworkCore; namespace EFCoreMigration.Models { public class EFContext : DbContext { private const string connectionString = "Server=(localdb)\\mssqllocaldb;Database=EFCoreMigration;Trusted_Connection=True;"; protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(connectionString); } public DbSet<Product> Products { get; set; } public DbSet<Vendor> Vendors { get; set; } } } |
The connection strings are hardcoded in this example for simplicity. In real-life applications, you should use the entity framework core connection string.
Preparing for Migration
There are two ways by which you can perform migrations
- From Package Manager Console of Visual Studio
- Using Command-line tools dotnet.exe
From Package Manager Console
To use the Package Manager Console, you need to install the Entity Framework Core Tools. Open the Package Manager and run the following command.
1 2 3 | Install-Package Microsoft.EntityFrameworkCore.Tools |
Installing the above package also installs the Microsoft.EntityFrameworkCore.Design package. This package actually contains the commands like add migrations, Update database, scaffold an existing database by reverse-engineering the schema of a database, etc
In ASP.NET Core 2.1 and above Visual Studio 2017 or Visual Studio 2019 automatically includes the above packages.
Using Command Line Tools
The Command line tools run from the command line and do not require the Visual Studio. If you are not using Visual Studio, then using the command line tools is the only option available to you.
.NET Core SDK version 2.1.300 and newer
If you are using the .NET Core SDK version 2.1.300 or newer you do not have to do anything. The tools have become part of the SDK. You can download the SDK from the link
Previous Versions
To Tools to work correctly, you need to install the Microsoft.EntityFrameworkCore.Design package. Go to to the solution directory and run the following command
1 2 3 4 | dotnet add package Microsoft.EntityFrameworkCore.Design dotnet restore |
Once you installed them, you can continue with the migration
List of Migration Commands
| PMC Command | CLI Commands | Remarks |
|---|---|---|
| Add-Migration | dotnet ef migrations add | Adds a new migration. |
| Drop-Database | dotnet ef database drop | Drops the database |
| Get-DbContext | dotnet ef dbcontext info | Gets information about a DbContext type. |
| Remove-Migration | dotnet ef migrations remove | Removes the last migration |
| Scaffold-DbContext | dotnet ef dbcontext scaffold | Scaffolds a DbContext and entity types for a database. |
| Script-Migration | dotnet ef migrations script | Generates a SQL script from migrations. |
| Update-Database | dotnet ef database update | Updates the database |
| NA | dotnet ef dbcontext list | Lists available DbContext types. |
| NA | dotnet ef migrations list | Lists available migrations. |
add migration
Whenever you create or Modify the domain classes, then the first thing you need to do is to create a Migration. This is done by add-migration command (or by using dotnet ef migrations add command line)
Open the Package Console manager and run the command
1 2 3 | add-migration "initial" //Package manager Console |
1 2 3 | dotnet ef migrations add "initial" //Dot net cli |
Where initial is the name of the migration
If the context is not part of your startup project, then for the migration to work correctly
- You must install Microsoft.EntityFrameworkCore.Tools in the startup project
- The reference to the project containing the context must present in the startup project
- The Solution must compile without any errors
- You must select the Project containing the context as the default Project in the package manager console
Common Errors
The two common errors that you may face while running the add-migration are
The term 'add-migration' is not recognized as the name of a cmdlet, function, script file, or operable program.
Solution: install the Microsoft.EntityFrameworkCore.Tools
add-migration : Build failed.
Solution: Rebuild the entire solution and correct any errors. The migration will not run if there is an error in the solution.
your target project doesn't match your migrations assembly
Solution: This error arises when you have a separate project for the context class. In such cases ensure that you set the project containing the context as default project in PMC.
Inspecting the Migration scripts
The add-migration command created three classes under the folder Migrations. One is the Migration class and the second one is the designer class & the last one is snapshot class.
The Migration class
The Migration class file is named as <TimeStamp >_<MigrationName>.cs and the class name is the same as the Migration name.
It is a partial class and inherits from the Migration base class
This class has two methods. The Up() & the down()
The up method has the operations that will apply the current migrations to the database so that the database becomes in sync with this migration
The down method undo whatever the Up method so that the current migration is removed and database reverts to its previous state
The Designer class
The Designer class file named as <TimeStamp >_<MigrationName>.Designer.cs and the class name is the same as the Migration Name.
This class has one method BuildTargetModel(), which uses an instance of ModelBuilder and contains the state of the model expressed using the Fluent API
Snapshot class
The snapshot class file is named as ModelSnapshot.cs and the context name is used as the class name. It contains the latest state of the model expressed using the Fluent API.
The add-migration command generates this file when it is run for the first time. In the subsequent runs, it uses it to find out the changes to be made to the model. It then overwrites it so that it remains up to date.
Update database
The add-migration creates the migration package but does not create the database.
The update-database command is used to bring the database in sync with the latest migration or the migration name specified as the argument
Run the update-database as shown in the image above.
1 2 3 | update-database //Package Manager Console |
1 2 3 | dotnet ef database update //Dot Net CLI |
Open the SQL Server and you will see that the tables Products & vendors are created in the Database. Also, note that the _EFMigrationsHistory table.
__EFMigrationsHistory
This table keeps track of the Migrations applied to the database. It contains two columns one is MigrationId and ProductVersion. MigrationId contains the Unique ID of the migration and ProductVersion is the version number of Entity Framework used.
The update-database checks if the database exists. If the table __EFMigrationsHistory does not exists then the update-database applies the migration from the beginning and then creates the __EFMigrationsHistory
If __EFMigrationsHistory already exists, then update-database checks the to see the last migration applied and then continues to apply the migrations from the last migrations to the latest. It then inserts latest migration id in the __EFMigrationsHistory table
Update-database never checks the database schema to verify whether it matches with the model. So if you accidentally delete the __EFMigrationsHistory table, alter or delete any other table or fields, the EF Core never reports an error. The error occurs only when the App is running and you query the altered/deleted table
Connection string for update-database
The update-database requires us to create the OnConfiguring() method in the context class and expects us to pass the correct connection string to the desired database provider. For example if you are using the Sql Server then you need to call the method UseSqlServer(ConnectionString)
That is because the update-database instantiates the Context class and executes the onConfiguring method so as to configure the context. It is unaware of how and where you have stored the connection string. And it does not have the access to Dependency injection container as it runs outside the project.
There are many ways you can handle this scenario. One of the ways is to read the configuration file in the OnConfiguring method as shown below
1 2 3 4 5 6 7 8 9 10 11 12 | if (!optionsBuilder.IsConfigured) { var builder = new ConfigurationBuilder(); builder.AddJsonFile("appsettings.json", optional: false); var configuration = builder.Build(); string connectionString = configuration.GetConnectionString("SQLConnection").ToString(); optionsBuilder.UseSqlServer(connectionString); } |
And also remember to select the select the appsettings.json and right click to select the properties. Select the property “Copy to output directory” and change the value “copy always” or “copy if newer”
Add Migration again
Let us add Rate field to the Product model.
1 2 3 4 5 6 7 8 9 10 11 | namespace EFCoreMigration.Models { public class Product { public int Id { get; set; } public string Name { get; set; } public decimal Rate { get; set; } } } |
Now, run add-migration again. Let us give the name V1 to the migration
1 2 3 | add-migration "V1" //Package Manage Console |
1 2 3 | dotnet ef migrations add "V1" //Dot net cli |
The command now creates new migration & designer classes. But it overwrites the snapshot class.
Here is the migration class with up & down method. You can clearly see that up method has an AddColumn method and down method has DropColumn method
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | public partial class V1 : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.AddColumn( name: "Rate", table: "Products", nullable: false, defaultValue: 0m); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropColumn( name: "Rate", table: "Products"); } } |
Now, run the update-database to update the database again with the new migrations.
Open the database and verify that the rate column appears in the Product table. Check the __EFMigrationsHistory table. You will find a new record with the new MigrationId.
Remove Migration
You can remove the migration by using the remove-migration command. The remove-migration command removes the last migration applied.
If the migrations are already applied to the database, then you will get the following error message
“The migration ‘20180927070534_V1’ has already been applied to the database. Revert it and try again. If the migration has been applied to other databases, consider reverting its changes using a new migration.”
In such a scenario, we first need to revert database update to one prior to the V1 i.e. Initial in our case.
Revert Migration
To revert a migration run the update-database with the name of the migration, to where you want to revert back.
For Example, we have created two migration initial & V1. To remove the V1 migration means, we are reverting back to Initial. Hence run the following command
1 2 3 | update-database "Initial" // Package Manager Console |
1 2 3 | dotnet ef database update "initial" //Dot net cli |
Now, you can remove the migration V1 by using the command
1 2 3 | remove-migration |
This will remove the migrate & designer file generated and reverts the snapshot file
run update-database 0 to remove all updations applied to the database. It empties all the tables except __EFMigrationsHistory table
Script Migration
Most of the time it is not possible to use the update-database command as you may not have access to the production server. In such cases, we can generate SQL script and execute it manually on the production server.
To Create SQL script, we make use of the script-migration command.
1 2 3 | script-migration //Package Manager Console |
1 2 3 | dotnet ef migrations script //Dot net cli |
The following is the sample script from the script-migration command. The advantageous here is you can inspect the script and may insert additional checks & queries and distribute it to the production server
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL BEGIN CREATE TABLE [__EFMigrationsHistory] ( [MigrationId] nvarchar(150) NOT NULL, [ProductVersion] nvarchar(32) NOT NULL, CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId]) ); END; GO CREATE TABLE [Products] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Products] PRIMARY KEY ([Id]) ); GO CREATE TABLE [Vendors] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Vendors] PRIMARY KEY ([Id]) ); GO INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20180927033248_initial', N'2.1.3-rtm-32065'); GO |
The script migration does not have access to the database. It does not stop you from applying the migration again. Or apply an old migration. Hence we need to take care of that issue. To ensure that it does not happen make use of the --idempotent flag as shown below
1 2 3 | Script-Migration -i |
You can also generate the script by choosing the range of migration using the argument -from and -to as shown below
1 2 3 | Script-Migration V4 V6 |
Learn more about script migration
References
Read More
Summary
That concludes our tutorial on ef core migrations in entity framework core. In the next tutorial, we will look at how to reverse engineer an existing database using the migrations
what is the command to add migration , if there are more than one DBContext.
Add-Migration InitialCreate -Context MySqliteDbContext -OutputDir Migrations\SqliteMigrations
dotnet ef migrations add InitialCreate –context MySqliteDbContext –output-dir Migrations/SqliteMigrations
Please refer to https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/providers?tabs=vs