C# – Migrations in Entity Framework in a collaborative environment

We have multiple developers working on a project that uses Entity Framework 5.0. Every developer uses his own local SQL 2012 database so he can develop and test without impeding others.

At first, we used a hybrid of automatic migrations and code-based migrations. That didn't work well at all so we decided to disable automatic migrations and to only allow code-based. I should add that we started again with a clean database without a 'corrupted' _MigrationsHistory from all the automatic migrations.

So now the workflow is:

1. Developer changes his datamodel
2. Does add-migration <Name> and applies it to his database with update-database.
3. Checks in the datamodel change and the migration into Git.
4. Another developer pulls, receives the changes and applies it to his database.

So far, this worked well. However before today it was usually just me who made the migrations and the others applied them. But today there were migrations from three developers. I just pulled those migrations, did an update-database which went fine.

I also had a change to my own datamodel however so at the end of the update-database it gave me a warning that I still wasn't up to date so I did add-migration <my migration>. However when it scaffolded the migration, it gave me the changes of all the migrations I had already applied to the database. So: it tried to drop columns that had already been dropped, tried to create a table that already existed, etc.

How can that be? My assumption was that EF would just check the _MigrationsHistory table and find out which migrations weren't present in the table yet and apply those one by one ordered by the timestamp that's part of the name. But apparently not, because even when I undo my own changes and I have a clean environment it still complains my database isn't in sync with the model. But I just pulled those changes and applied them to my database. It is in sync. I can see the migrations that I just applied in the _MigrationsHistory table too.

The only thing I can think of is that I added a property to a datamodel that wouldn't result in a database change (I added a List<X> to datamodel Y where X is the many in the one-to-many relationship. This wouldn't result in a database change as X already had a foreign key to Y). Could that be it? If so, that's really fragile because there's no way to add a migration for that since there's no database change and I'm not sure how to fix this either.

I'm not sure how to deal with this, because I can of course just edit what it scaffolded and remove everything that has already been applied to my database. But then what? I check it in and then some other developer gets the same message that his database isn't up to date even after applying my new changes, scaffolds his own changes, gets the same nonsense scaffolding, edits it, checks it in and then the next developer gets it. It becomes a vicious circle and a similar one to what we had when we used automatic migrations and I thought we had fixed that by switching to code-based only. I can't trust it right now to do the right thing and it's a nightmare to work with like this.

What I also tried is adding the migrations I pulled from my coworkers one by one with update-database -t:201211091112102_<migrationname> but to no avail. It still gives me the erroneous scaffold.

So what did we do wrong here, or is EF simply not built for collaboration like this?

UPDATE

I created a reproducible test case, it's a bit of a lengthy dance though in order to simulate this multi user/multi database scenario.

https://github.com/JulianR/EfMigrationsTest/

Steps to reproduce when you have the above project (these steps are also present in the code):

1. add-migration Init
2. update-database (on database 'TestDb')
3. Change connection string to point to TestDb1
4. update-database on TestDb1
5. Uncomment property Foo on class Test
6. add-migration M1 to add property Foo to TestDb1
7. Comment out Test.Foo again
8. Change connection string to point to TestDb2
9. Exclude migration M1 from project so it doesn't get applied to TestDb2
10. Uncomment property Bar on class Test
11. update-database to apply Init migration to TestDb2
12. add-migration M2 to add property Bar to TestDb2
13. Change connection string to point to the original TestDb again
14. Include migration M1 into the project again
15. Uncomment property Foo on class Test
16. Uncomment property SomeInt on class Test
17. update-database
18. add-migration M3
19. update-database, get an error because M3 tries to add column Foo to database TestDb which was already just added by migration M1.

The above is to simulate three users, where user 1 inits his database, the other two use his initialization to create their database as well. Then user 2 and user 3 both make their own change to the datamodel and add it to source control together with the migrations needed to apply the changes. Then user 1 pulls the changes of user 2 and 3 while user 1 has also made a change to the database himself. Then user 1 calls update-database to apply the changes of user 2 and 3. He then scaffolds his own migration which then erroneously adds a change from user 2 or 3 to the scaffolded migration which causes an error when applied to user 1's database.