EF Migrations and a Merge Conflict

To put a bit more stress on EF Migrations I’ll simulate a scenario that should be quite common in a multi developer environment.

  1. Developer A and B gets the latest code from the repository.
  2. Developer A adds a column and checks in a migration.
  3. 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.

This post is part of the EF Migrations series.<< Indexes in Code-Based EF MigrationsPrevent EF Migrations from Creating or Changing the Database >>

  • Pingback: Entity Framework Interview Questions and Answers - Tech Career Source on 2017-06-11
  • MaxB on 2012-04-12

    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.

  • Peter Jaffe on 2012-06-14

    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 an update-database targetmigration:car_topspeed which should have been sufficient to allow an add-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 the Up() and Down() 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?

    • Anders Abel on 2012-06-15

      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:

      • Always get the latest code before checking in any migration.
      • Always place the migration to be checked in last in the migration list. If needed, the dates are updated. Then the designer file is re-scaffolded to make sure that the checksum matches the model.

      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.

  • Srdjan on 2014-08-17

    For bit more details regarding merging for EF Migrations follow this link:
    http://msdn.microsoft.com/en-us/data/dn481501.aspx

    • Anders Abel on 2014-08-17

      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.

  • arun on 2016-12-23

    wouldn’t the data get cleared if we revert back

    • Anders Abel on 2017-01-04

      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.

  • Manoj Mathe on 2017-01-08

    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 ??

    • Anders Abel on 2017-01-19

      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.

  • Leave a Reply

    Your name as it will be displayed on the posted comment.
    Your e-mail address will not be published. It is only used if I want to get in touch during comment moderation.
    Your name will be a link to this address.
Software Development is a Job – Coding is a Passion

I'm Anders Abel, an independent systems architect and developer in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

Code for most posts is available on my GitHub account.

Popular Posts

Archives

Series

Powered by WordPress with the Passion for Coding theme.