Entity Framework 6 Model-First - Update Production Database Based On Local Changes

Question

I'm new to EF and inherited a project that apparently used database-first development.

The production environment uses SQL Azure and I'm basically trying to figure out the standard approach for updating its schema.

At first, I tried enabling migrations for the EF project, before attempting the process described here: http://www.dotnet-tricks.com/Tutorial/entityframework/R54K181213-Understanding-Entity-Framework-Code-First-Migrations.html ...but I discovered that only works for code-first development.

Now what I plan to do is to make changes to the database that I'm running in my local dev environment, then use my local database to update the model in my EF project.

From there, I'd like to deploy the changes to the SQL Azure environment while preserving all the existing records in the database, but the best way to do so is unclear.

Is there anything in the EF tooling that will generate update scripts that don't drop tables? Do I need to write update scripts manually? Do people typically use SQL Server tools to do the update instead?

My initial Googling hasn't found the answer to the question, unfortunately, though it seems like something that should be an extremely common problem.

Answer 1

EF migration is used for code first development, not database first. But you can still use it if you do reverse engineering from database. To be able to do so you need to install The Entity Framework 6 Tools, but this will need some efforts, like

• Reverse engineering source database
• Generate to new database by using EF migration (Enable-Migrations, Add-Migration InitialCreate, Update-Database ConnectionStringName NewDatabase)
• Reverse engineering the target database (the database that has been modified)
• Generate upgrade script (Enable-Migrations, Add-Migration UpgradeToVersionX, Update-Database ConnectionStringName NewDatabase -Script)

Also EF database reverse engineering still has problem related to unique constraint.

---

But since you are working with database first which you modify the schema from database directly, the easier way would be using Schema Comparer of the SQL Server Data Tools.

• Create new project of SQL Server Database Project (from Other Languages -> SQL Server)
• Right click on the project choose Schema Compare
• Select source database and target database
• Run Generate Script

The generated script would be as close as writing the script manually, probably less human error, like typo.