Andrew Lock wrote an excellent series called “Running async tasks on app startup in ASP.NET Core” in which he used “migrating a database” as an example of something you could do on startup. In part 3 of the series he covered why migrating a database on startup isn’t always the best choice. I decided to write a series about the different ways you can safely migrate a database, i.e. change the database’s schema, using Entity Framework Core (EF Core).
This is the first part of the series, which looks how to create a migration, while part 2 deals how to apply a migration to a database, specifically a production database. I have used a similar approach to Andrew’s when writing this article, i.e. I try to cover all the way to create a migration script when working with EF Core with their pros, cons and limitations.
NOTE: Andrew and I know each other as we were both writing books for Manning Publications at the same time: Andrew’s book is “ASP.NET Core in Action” and mine is “Entity Framework Core in Action”. We shared the toils and joys of being an author, but Andrew has the harder job with ASP.NET Core – his book is 700 pages long and mine is “only” 500 pages long.
TL;DR – summary of creating a migration
NOTE: Click the links to go directly to the section covering that point.
- There are two types of migrations that can be applied to a database:
- Adds new tables, columns etc, known as a non-breaking change (easy).
- Changes columns/tables and needs to copy data, known as a breaking change (hard).
- There are five ways to create a migration in EF Core
- Use EF Core to create a migration – easy, but doesn’t handle all possible migrations.
- Use EF Core to create a migration and then hand-modify the migration – medium to hard, but handles all possible migrations.
- Use a third-partly migration builder to write a C# migration – hard, as you need to write the migration yourself, but you don’t need to know SQL.
- Use a SQL database comparison tool to compare databases and output a SQL changes script – easy, but you do need some understanding of SQL.
- Write your own SQL migration scripts by copying EF Core’s SQL – hard-ish, gives great control but you do need to understand SQL.
- How to be sure your migration is valid – use CompareEfSql tool.
Setting the scene – what questions should we ask about creating a migration?
There are lots of ways to migrate a database’s schema, and in development you can use almost any approach. But when it comes to migrating a production database (i.e. the one that is being used by real users) then it becomes very serious. Getting it wrong will, at the minimum, inconvenience your users and at worse mangle or lose you (precious) data in your database!
But before we get the scary part of updating a database schema we need to build the migration script that will contain the schema, and possibly data, changes. To build the appropriate migration script we need to ask ourselves some important questions about the type of changes we need to apply to the database. Is the required migrations going to be:
- A non-breaking change, i.e. it only adds new columns, tables, etc., which could be applied while the old software was still running, i.e. the old software would work (not break) with the database after the migration.
- A breaking change, i.e. some data must be copies or transformed during the migration, could NOT be applied while the old software, i.e. the old software would encounter errors (break) with the database after the migration.
The other part in this article is that we are working with EF Core, which brings both some benefits and constraints. The benefits are that EF Core can, in most cases, create the required migrations automatically. The constraints are that the database after a migration has been applied must match the software Model of the database that EF Core builds by looking at your DbContext and mapped classes – I refer to EF Core’s Model with a capital M, because there is a property called Model in the DbContext that contains the complete mappings between classes and the database.
NOTE: I’m going to cover migrations where you are in control of the control of the classes mapped to the database and the EF Core configuration – sometimes known as the Code-First Approach. The other alternative, which I’m NOT going to cover, is where you directly control the database and you use an EF Core command called scaffolding to create the entity classes and EF Core configuration for you. That’s because migrations are trivial – just re-scaffold your database.
PART1. The five ways to create a migration script
As I said in the last section, any migration script we create must migrate the database to a state that matches EF Core Model. For instance, if a migration added a new column to a table then the entity class mapped to that table must have a property to match that new column. If EF Core’s Model of the database schema does match the database, then you can get errors occurring in queries or writes. I refer to the migration script as creating a “valid” database if it matches EF Core’s Model of that database.
The validity of a migrations created by EF Core isn’t in doubt – EF Core created it so it will be valid. But if we need to edit a migration, or we take on the job of building the migration ourselves, then we need to be very careful that the migration creates a “valid” database as far as EF Core is concerned. This is something I have thought about a lot.
This is the list of ways you can create a migration script.
- Creating C# migration script
- Standard EF Core migration script: use EF Core’s Add-Migration command to create a C# migration script.
- Hand-modified EF Core migration script: Use EF Core’s Add-Migration command to create a C# migration script and then hand-edit it to add the bits that EF Core missed.
- Use a third-partly migration builder, e.g. FluentMigrator. Tools like this allow you to write your own migration script in C#.
- Creating SQL migration script.
- Use a SQL database comparison tool. This compares the last database schema with the schema of a new database created by EF Core and produces a SQL script that will migrate the old database to the new database schema.
- Write your own SQL migration scripts. A competent writer of SQL could produce a SQL migration scripts, with some help by capturing the SQL EF Core would use to create a database.
Here is a summary diagram to give you an overall review of the five approaches, with my personal view on the ease of use and level of limitations.
Now let’s look at each one of these in turn.
1a. Standard EF Core C# migration script
This is the standard migration technique provided by EF Core. It’s well documented by Microsoft, but in summary you run a command called Add-Migration which adds three C# files to your application that contain the changes needed to migrate an existing database using migraions to match the current EF Core setup/configuration.
Good parts | · Builds a migration automatically · You don’t need to learn SQL · Includes a revert migration feature |
Bad parts | |
Limitations | · Standard migration cannot handle breaking changes (but see 1b). · No handling of SQL features, e.g. SQL user defined functions (but see 1b). |
Tips | · Watch out for error messages when you run the Add-Migration method. If EF Core detects a change that could lose data it will output an error message, but still creates the migration files. YOU MUST alter the migration script otherwise you will lose data – see section 1b. · If your DbContext is in another assembly from where the DbContext is registered you need the MigrationsAssembly method on your build and you most likely you need to implement a IDesignTimeDbContextFactory in the DbContext assembly. |
My verdict | This is a really easy way to handle migrations and it works well in many cases. The problem is, what happens if the migration doesn’t cover what you want. Thankfully there are many ways to handle that. |
Reference: Microsoft’s documentation on creating a migration.
1b. Hand-modified EF Core C# migration script
The nice thing about EF Core’s Add-Migration command is it creates the C# migration files as the starting point, but you can then edit these files yourself to add code to handle breaking changes or add/update SQL parts of the database. Microsoft give an example of handling a breaking change with copying data.
Good parts | Same as standard migration + · Ability to customise the migration. · Ability to include SQL features, e.g. SQL user defined functions. |
Bad parts | · You need to understand what is happing in the database. · Can be difficult to decide on how to edit the file, e.g. do you keep everything EF Core added and then alter it, or remove EF Core parts and do it yourself? |
Limitations | No simple way to check the migration is correct (but see CompareEfSql later). |
Tips | Same as standard migration. |
My verdict | Great for small alterations, but big changes can be hard work as you are often mixing C# commands with SQL. That is one reason why I don’t use EF Core migrations. |
Reference: Microsoft’s example of hand-modifying a migration.
1c. Use a third-partly C# migration builder
It was Andrew Lock who pointed out to me an approach using the FluentMigrator (see this documentation) to write your migrations. This works similarly to EF migrations, but you have to do all the hard work of detailing the changes. The good thing is FluentMigrator’s commands are very obvious.
Good parts | · Don’t need to learn SQL. · Very obvious what the changes are, i.e. “code as documentation”. |
Bad parts | · You must work out what the changes are yourself. · Not guaranteed to produce a “correct” migration (but see CompareEfSql later). |
Limitations | – none – |
Tips | Note that FluentMigrator has a “Migration Runners” which can apply the update to the database, but it can also output SQL scripts instead. |
My verdict | No real experience myself. It feels like it is a clearer syntax that EF Core’s migration, but you have to do all the work yourself. |
Reference: FluentMigrator of GitHub.
2a. Use SQL database comparison tool
There are free and commercial tools that will compare two databases and create a SQL change script that will migrate the old database schema to the new database schema.
A “free” comparison tool is built into Visual Studio 2017 (all versions) called SQL Server Object Explorer, under the View tab. If you right-click on a database, then you can access the “Compare Schema” tool (see figure above right) which can produce a SQL change script.
The SQL Server Object Explorer tool is very good, but there isn’t much documentation on this (pity). Other commercial systems include Redgate’s SQL Compare.
Good parts | Builds the correct SQL migration script for you. |
Bad parts | · You need a little bit of understanding of databases. · No all SQL compare tools produce a revert script. |
Limitations | Does not handle breaking changes – needs human input. |
Tips | Watch out for SQL compare tools that outputs every setting under the sun to make sure it gets it right. EF Core’s migrations are straightforward, like “CREATE TABLE…”, so should do that same. If you have any specific settings, then build them into the database create. |
My verdict | I have used SQL Server Object Explorer on a big migration that was hard to hand-code. Very useful and especially good for people who aren’t comfortable with the SQL language. |
2b. Hand-coding SQL migration scripts
This sounds really hard – writing your own SQL migrations, but there is plenty of help on hand, either from SQL compare tools (see above) or by looking at the SQL EF Core would use to create the database. This means I have SQL I can look at and copy to build my SQL migration script.
Good parts | Total control over the database structure, including parts that EF Core won’t add, e.g. user defined functions, column constraints, etc. |
Bad parts | · You must understand basic SQL like CREATE TABLE etc. · You must work out what the changes are yourself (but there is help) · No automatic revert migration. · Not guaranteed to produce a “correct” migration (but see CompareEfSql later). |
Limitations | – none – |
Tips | · I use a unit test that captures the logging output of EF Core’s EnsureCreated method. That gets me the actual SQL EF Core would output. I then look for the differences for the last database. That makes writing the SQL migrations much easier. · I unit test a migration by creating a database by applying all the migrations, including the new migration, and then running CompareEfSql to check the database matches EF Core’s current Model of the database. |
My verdict | This is what I use, with a little help from a CompareEfSql tool. If EF Core’s migration feature is so good, why do I go to all this trouble? Here are my reasons: · Total control over the database structure, including parts that EF Core won’t add, e.g. user defined functions, column constraints, etc. · Because I am writing the SQL it makes me think about the database aspects of the change – should that property be nullable? do I need an index? etc. · Handing breaking changes by hand-modifying EF Core’s migration system isn’t that easy. I might as well stick with SQL migrations. This is for developers that wants total control and visibility of the migrations. |
You can capture the SQL output by EF Core’s for creating a database but capturing the EF Core’s logging while calling the method EnsureCreated (the EnsureCreated method is meant for creating unit test database). Because setting up logging for EF Core is a little bit complex I added helper methods to my EfCore.TestSupport library to handle that. Here is an example unit test that creates a new SQL database and catches the SQL commands that EF Core produces.
[RunnableInDebugOnly] public void CaptureSqlEfCoreCreatesDatabaseToConsole() { //SETUP var options = this.CreateUniqueClassOptionsWithLogging<BookContext>( log => _output.WriteLine(log.Message)); using (var context = new BookContext(options)) { //ATTEMPT context.Database.EnsureDeleted(); context.Database.EnsureCreated(); } }
Let’s look at each line of this code
- Line 5. This is an EfCore.TestSupport method that creates the options for your DbContext. This version uses a database name that includes the class name. I do this because xUnit test classes are run in parallel, so I want a unique database for this unit test class.
- Line 6. I use the version of the option builder ending with …WithLogging, which allows me to capture the log outputs. In this case I output the Message part of the log directly to the unit test output window.
- Lines 11 & 12. First, I ensure the database is deleted so that when I call EnsureCreated a fresh database will be created with a schema defined by the current DbContext’s configuration and mapped classes.
Below is part of the output captured in the unit test output. This provides you with the exact SQL that EF Core would use to create the whole schema. You do need to extract just the parts that relate to your migration, but at least you can cut-and-paste the parts you need into your SQL migration script.
CREATE DATABASE [EfCore.TestSupport-Test_TestEfLogging]; Executed DbCommand (52ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [EfCore.TestSupport-Test_TestEfLogging] SET READ_COMMITTED_SNAPSHOT ON; END; Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Authors] ( [AuthorId] int NOT NULL IDENTITY, [Name] nvarchar(100) NOT NULL, CONSTRAINT [PK_Authors] PRIMARY KEY ([AuthorId]) ); Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Books] ( [BookId] int NOT NULL IDENTITY, [Title] nvarchar(256) NOT NULL, -- rest of SQL left out
How to be sure your migration is valid – use CompareEfSql tool
I have mentioned CompareEfSql a few times in the descriptions of creating migrations. This tool compares a database with the model of the database that EF Core creates on first use for a DbContext. This model, accessed via the Model property in the DbContext instance, is built up my EF Core by looking at the DbContext configurations and DbSet and DbQuery properties.
This allows a developer to test an existing database against EF Core Model and gives you error messages if they are different. I find this a very powerful tool which allows me to hand-code SQL migrations and be sure that they are correct (there are some small limitations). Here is an example unit test that will fail if the database schema doesn’t match EF Core’s Model.
[Fact] public void CompareViaContext() { //SETUP var options = … options that point to the database to check; using (var context = new BookContext(options)) { var comparer = new CompareEfSql(); //ATTEMPT //This will compare EF Core model of the database //with the database that the context's connection points to var hasErrors = comparer.CompareEfWithDb(context); //VERIFY //The CompareEfWithDb method returns true if there were errors. //The comparer.GetAllErrors property returns a string //where each error is on a separate line hasErrors.ShouldBeFalse(comparer.GetAllErrors); } }
I love this tool, which is in my EFCore.TestSupport open-source library. It allows me to build migrations and be sure they are going to work. I also run it as a normal unit test and it tells me immediately if I, or another team mate, has changed EF Core’s setup.
You can get a much longer description of this tool in the article called EF Core: taking full control of the database schema and its many features and configurations can be found in the CompareEfSql documentation pages.
NOTE: I build a version of this originally for EF6.x (see this old article), but it was limited because EF6.x didn’t fully expose its internal model. With EF Core I could do so much more (EF Core rocks!) and now I can check almost everything, and because I tap into EF Core’s scaffolder service it works for any database that EF Core supports (see this doc).
Conclusion – Part 1
This part of the series covers creating a valid migration, while part 2 deals with applying a migration to a database. This article lists all the applicable approaches to creating a database migration when working with EF Core – with their pros, cons and limitations. And as you can see EF Core’s Add-Migration command is really good, but it doesn’t cover every situation.
Its up to you to decide what types of migrations you might encounter, and what level of control do you want over the database schema. If you can get away with just using EF Core’s standard migrations (1a) then that makes life easier for you. But if you expect breaking-changes, or you need to set up extra SQL features then you now know the options available to you.
The scary part comes in the part2 – applying a migration to a production database. Changing a database which contains business-critical data needs (demands!) careful planning and testing. You need to think about what you are going to do if (when!) a migration fails with an error.
The original reason I moved away from EF migrations in EF6 was its automatic migration on startup was working fine, until it threw an error on deployment! It was really hard to track down an error in the migration – that alone got me moving away from using EF migrations (read this old article on my thoughts back then).
EF Core’s migration handling is better than EF6: automatic migrations have (thankfully!) gone and EF Core migration are more git-merge friendly, to mention just two changes. But the way I build SQL migration scripts makes me think much more carefully about what I am doing than just running Add-Migration. EF Core is a really great O/RM but it does hide the database too well sometime. Creating SQL migration scripts makes me think through a migration from the database point of view, and I often spot little tweaks to the database, and the C# code, to make the database better or more robust.
Now go to part 2 to see how to apply a migration to a database.
I feel that the “Hand modified EF core migration” should also include the simpler step of including custom SQL in the generated migration code, without having to look or edit the generated SQL. (which does look rather gnarly)
This can handle quite a few “breaking changes”, as in the example below, where we drop the FirstName and LastName columns and create a new FullName column that consists of a concatenation of FirstName and LastName. (Leaving aside the wisdom of such an operation; personally I’d add a “DispalyName” column, since some people have last names with spaces and reverting the migration would probably mess up some names)
migrationBuilder.AddColumn<string>(
name: “FullName”,
table: “Customer”,
nullable: true);
migrationBuilder.Sql(
@”
UPDATE Customer
SET FullName = FirstName + ‘ ‘ + LastName;
“);
migrationBuilder.DropColumn(
name: “FirstName”,
table: “Customer”);
migrationBuilder.DropColumn(
name: “LastName”,
table: “Customer”);
This is from the example here:
Managing Migrations – EF Core | Microsoft Docs
Hi Thomas,
Thanks for adding that example. I agree that hand-modified migrations are really useful and I cover that in detail in my book, but the article was getting quite long and left that out.
Thanks for Detailed article.
I am new to EF core, so I have question, is it possible to create database and use EF Core to map it in project? Like Edmx?
I googled it and didn’t found a good example.
If it is not possible, how do we create migration and add database connection using EF code using database first approach.
We already have database and want to connect it to EF Core/.NET Core project.
Thanks
Hi Vikas,
Sorry, I should have pointed out that this article is how to migrate a database that you already have linked to EF Core. That means I left out the feature you need!
Yes, EF Core has a tool that can read an existing database and build the EF Core code to match it. This is called reverse engineering, and here is a link to the Microsoft docs on this.
There is also a more friendly tool for Visual Studio called EF Core Power Tools, which is very nice to use and has other features such a creating a diagram of your database.
I hope that helps.
Thanks for useful links.
Standard migration can handle breaking changes. Why do you think they cannot?
Hi Igor,
I say that standard migrations (1a in my article) can’t handle breaking changes because they don’t output code to copy data from the original schema to the new schema, e.g. you want to move a column to another table. In fact when you create the migration it will warn you if it sees something which will lose data.
You can of course hand-edit the migration (1b in my article) then it can handle that sort of migration.
PS. if you want to see a breaking change I show some in the second article in this series, for instance the complexities of applying a breaking change to the database while the application is still running, see https://www.thereformedprogrammer.net/handling-entity-framework-core-database-migrations-in-production-part-2/#applying-breaking-changes-to-a-continuous-running-application-the-five-stage-app-update
Ah, I see. Thank you for the explanation. But standard migrations can handle at least column removals, column renames and table renames without any hand-edit. Probably more accurate phrasing would be “doesn’t handle some of breaking changes”?
Great article! Thanks !
Glad you found it useful.
Got lots to learn considering I’ve been shying away from too much SQL all along. Thanks for this!
Hi Adebiyi,
My system lost your comment (Disqus is playing up). But glad you liked the articles