EF Migrations series
- Using Entity Framework to Create a Database
- Adding Indexes with EF Migrations
- Updating a Table with EF Migrations
- Indexes in Code-Based EF Migrations
- EF Migrations and a Merge Conflict
- Prevent EF Migrations from Creating or Changing the Database
- EF Code First Change Tracking
- EF Migrations Command Reference
- EF Code First Navigation Properties and Foreign Keys
- Update-Database MSI Custom Action
To put a bit more stress on EF Migrations I’ll simulate a scenario that should be quite common in a multi developer environment.
- Developer A and B gets the latest code from the repository.
- Developer A adds a column and checks in a migration.
- Developer B adds another column and makes everything ready for checkin. During checkin Deveveloper B finds out about Developer A’s checkin, gets the latest version and then checks in.
When working in a team this is a common scenario. Especially in green field development where the database continously is expanded as more and more features are added.
Developer A’s changes
For this example, Developer A’s changes are the added TopSpeed
column from the previous post in this series. In the simulated scenario in this post, everything in that post was done by Developer A. In this post I’ll focus on Developer B.
Switch to Developer B
To simulate the situation of Developer B I get the second latest version of the code, without Developer A’s changes from subversion (yes, my test code is under source control, what did you expect?). Then I revert the database migration.
PM> update-database -verbose -targetmigration:ix_car_brandid Using NuGet project 'TestLib'. Using StartUp project 'TestLib'. Target database is: 'Cars' (DataSource: ., Provider: System.Data.SqlClient, Origin: Configuration). Reverting migrations: [201202221302479_Car_TopSpeed]. Reverting explicit migration: 201202221302479_Car_TopSpeed. DECLARE @var0 nvarchar(128) SELECT @var0 = name FROM sys.default_constraints WHERE parent_object_id = object_id(N'Cars') AND col_name(parent_object_id, parent_column_id) = 'TopSpeed'; IF @var0 IS NOT NULL EXECUTE('ALTER TABLE [Cars] DROP CONSTRAINT ' + @var0) ALTER TABLE [Cars] DROP COLUMN [TopSpeed] [Deleting migration history record] |
The final step in simulating Developer B is to revert the code changes in subversion.
I’m now back to the state that Developer B starts from, happily unaware of Developer A’s checked in code changes.
Developer B’s update
Developer B also adds a column to the Cars table.
public class Car { // ... Old code goes here ... // Newly added property. [Required] [StringLength(20)] public string Color { get; set; } } |
A migration is added through the package manager in the same way as for Developer A. Now over to the exciting part. It’s time to get Developer A’s changes from source control. After saving all local changes I update to the latest revision. The change results in a couple of conflicts, one to the Car.cs file and the other in the project file. For both of them I choose to apply both set of changes to have both Developer A’s and Developer B’s changes.
Applying the migrations
Now with both set of migrations, that were created unaware of eachother, I’ll update the database.
PM> update-database -verbose Using NuGet project 'TestLib'. Using StartUp project 'TestLib'. Target database is: 'Cars' (DataSource: ., Provider: System.Data.SqlClient, Origin: Configuration). Applying explicit migrations: [201202221302479_Car_TopSpeed, 201202221335547_Car_Color]. Applying explicit migration: 201202221302479_Car_TopSpeed. ALTER TABLE [Cars] ADD [TopSpeed] [int] [Inserting migration history record] Applying explicit migration: 201202221335547_Car_Color. ALTER TABLE [Cars] ADD [Color] [nvarchar](20) NOT NULL DEFAULT '' [Inserting migration history record] Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration. You can use the Add-Migration command to write the pending model changes to a code-based migration. |
I did expect a massive error message, but EF Migrations handled this nicely. It added both columns just as it should. However it indicates that there are more database changes pending. Adding another migration will show what it is. This is the resulting file generated.
namespace TestLib.Migrations { using System.Data.Entity.Migrations; public partial class WhatIsMissing : DbMigration { public override void Up() { AddColumn("Cars", "TopSpeed", c => c.Int()); } public override void Down() { DropColumn("Cars", "TopSpeed"); } } } |
That’s exactly the same as the Car_TopSpeed migration. Obviously it is not properly recognized due to the conflicting changes. Let’s try to apply the migration.
PM> update-database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying explicit migrations: [201202221350450_WhatIsMissing]. Applying explicit migration: 201202221350450_WhatIsMissing. System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'TopSpeed' in table 'Cars' is specified more than once. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() Column names in each table must be unique. Column name 'TopSpeed' in table 'Cars' is specified more than once. |
That’s bad. It tries to reapply the migration, giving a huge error message. So what to do in this situation? The easiest is to avoid it completely by making sure that their are no conflicting updates being added. Before running add-migration MigrationName
the latest code should be refreshed. Then nobody else should add migrations in parallel.
Unfortunately that is not a reasonable requirement in a distributed environment. So what to do else? I’ll roll back my database to the state before any of the migrations and then try to find a way.
PM> update-database -targetmigration:ix_car_brandid -force -verbose Using NuGet project 'TestLib'. Using StartUp project 'TestLib'. Target database is: 'Cars' (DataSource: ., Provider: System.Data.SqlClient, Origin: Configuration). Reverting migrations: [201202221335547_Car_Color, 201202221302479_Car_TopSpeed]. Reverting explicit migration: 201202221335547_Car_Color. DECLARE @var0 nvarchar(128) SELECT @var0 = name FROM sys.default_constraints WHERE parent_object_id = object_id(N'Cars') AND col_name(parent_object_id, parent_column_id) = 'Color'; IF @var0 IS NOT NULL EXECUTE('ALTER TABLE [Cars] DROP CONSTRAINT ' + @var0) ALTER TABLE [Cars] DROP COLUMN [Color] [Deleting migration history record] Reverting explicit migration: 201202221302479_Car_TopSpeed. DECLARE @var0 nvarchar(128) SELECT @var0 = name FROM sys.default_constraints WHERE parent_object_id = object_id(N'Cars') AND col_name(parent_object_id, parent_column_id) = 'TopSpeed'; IF @var0 IS NOT NULL EXECUTE('ALTER TABLE [Cars] DROP CONSTRAINT ' + @var0) ALTER TABLE [Cars] DROP COLUMN [TopSpeed] [Deleting migration history record] |
Now I’m back to the state where Developer B just found out about Developer A’s changes. What Developer B needs now is a database and migration history that is at the state of Developer A. That can be done by a target-specific migration.
PM> update-database -targetmigration:car_topspeed -verbose Using NuGet project 'TestLib'. Using StartUp project 'TestLib'. Target database is: 'Cars' (DataSource: ., Provider: System.Data.SqlClient, Origin: Configuration). Applying explicit migrations: [201202221302479_Car_TopSpeed]. Applying explicit migration: 201202221302479_Car_TopSpeed. ALTER TABLE [Cars] ADD [TopSpeed] [int] [Inserting migration history record] |
Now Developer B has a database that is identical to where Developer A left it. To tell EF Migrations that Car_Color
should be run after Car_TopSpeed
we can re-scaffold the migration.
PM> add-migration Car_Color Scaffolding migration '201202221335547_Car_Color'. Only the Designer Code for migration '201202221335547_Car_Color' was re-scaffolded. To re-scaffold the entire migration, use the -Force parameter. |
The scaffolding tools is smart enough to only re-scaffold the designer file and that’s where the order of the migrations is kept. This is great. We have a way to re-base an existing migration when we find out that other developers have done work as well, without having to redo our entire migration code. Let’s try to update the database.
PM> update-database Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Applying explicit migrations: [201202221335547_Car_Color]. Applying explicit migration: 201202221335547_Car_Color. |
It worked like a charm.
Impressive
I’m impressed by EF Migrations handling this somewhat tricky situation so well. I had expected much less tool support, requiring hand editing of designer code to handle the situation. I’m impressed with EF Migrations so far and comfortable enough to use it in a real project. Nevertheless I will try to find out more tricky situations that occur during team development and maintenance and see if EF Migrations can handle it.
Thanks, it helped me to figure out why merging the second feature branch in git made migrations from the feature branch that I merged first obsolete.
Thanks for this blog post. It helped me think through this flow in a project I’m working on. I have a few bits of feedback on your post:
1. I think in your example you didn’t have to revert all the way back to
ix_car_brandid
, and instead you could have done anupdate-database targetmigration:car_topspeed
which should have been sufficient to allow anadd-migration Car_Color
to update Developer B’s model hash in the designer file. I believe your steps would completely work, but go a step further back in time than necessary.2. I see paths in my team development environment where Developer B may actually check in their migration for Color prior to seeing that there was a conflict with TopSpeed from Developer A. As a result, the whole team (and continuous build server) may have subsequently pulled down both developers independent migrations, putting everyone dead in the water with the combined persistent model not being compatible with their DBs. The easiest way that I see to fix this is for Developer B (or anyone for that matter) doing an
add-migration RealignModelHashDueToParallelMigrations
which will initially get a migration that attempts to redo previous migration actions (consistent with the original blog post above). Then manually delete the content of theUp()
andDown()
methods to clear that out and just retain the updated model hash. After checking this no-op migration in, all team members would pull that down and have that run on their development DBs bringing everyone back to an operational state.As an aside for those interested… in our project we are programmatically running the migrations during startup of the application for developers and the continuous build environments. We are going to generate SQL scripts out of the code migrations for upper level environments. Under this model, developers don’t have to think about using the EF commands in the package manager console unless they are explicitly the ones doing the code migrations (we aren’t doing automatic migrations). However, they would see startup errors in the DB compatibility check if there were conflicting parallel DB migration development by 2 or more developers.
Integrating a branch of development with its own code migrations seems like this problem taken to an extreme… How did you address that? Any chance it was as easy as my point #2 in my other post below, where you just create a final no-op code migration to include with the commit of your branch integration, and that just brings your model hash up-to-date?
You’re right in that I’m going one step further back than needed. The important thing is that Developer B gets a database that is in the same state as where Developer A left it. To get there, a migration back to car_topspeed is enough.
The solution you’ve outlined in 2) works for covering up the situation, but I prefer to not get there. In my dev. team we have two rules:
We strive for getting a migration sequence where every checked in version of the code is possible to build, deploy and run. Your point 2 strategy works to cover up the situation, but there will be some checked int version that cannot be run.
For the reintegration of branches we follow the same rules. When reintegrating the branch, the time stamps of the reintegrated migrations are changed to end up last in the migration list and the designer files are regenerated.
For bit more details regarding merging for EF Migrations follow this link:
http://msdn.microsoft.com/en-us/data/dn481501.aspx
That article is good, and much more detailed than this post. When I wrote this post, that article didn’t exist – I think it was about two years later that it was posted.
wouldn’t the data get cleared if we revert back
Yes, data can be lost when migrations are reverted. The method outlined here assumes that this is on development environments where the data is not valuable.
Definitely a great post.
Can you also add how this case has to be taken care when we have a automated build & deploy… How the migration scripts should be taken ??
This is a merge conflict and as all merge conflicts they must be resolved by a developer before checkin. Trying to do this in an automated way would most certainly result in data loss or corruption.
I don’t think this will work if A’s migration (topspeed) actually has a later timestamp than B’s migration (carcolor) – which is possible.