How to update structure of database with Entity Framework Code First without losing all data?

4
votes

I need to change structure (add more columns and change one of existing ones) of the database (I made some changes in "Models" of my WebAPI project). Is there some easy way to do this in Visual Studio 2013?

2
asp.netasp.net-mvcentity-frameworkasp.net-web-apivisual-studio-2013
Does all the data get lost when you update the schema? - George Stocker
You need to provide much more detail about your problem. The default behavior using Code First should be to retain your tables and data. It's possible that if you're both using automatic migrations and made some change that can't be applied without dropping and readding the table, that Entity Framework is doing this in order to migrate, but again, we'll need a lot more information to tell you anything definitively. - Chris Pratt
Are you using an Initializer class that seeds data? - markpsmith
I've updated the SQL database before, then updated the entity context with no data loss. - Humpy
In MVC 4 EF Code First, it is possible to change a database structure by installing something called database migrations. Typing in package console enable-migrations activate (or install) migrations. Then when you have changed the structure of the database in the code, typing update-database in the same console updates the structure of the database e.g. on SQL Server. - Celdor

2 Answers

2
votes

You can use Entity Framework Migrations and run the update-database command in order to generate a T-SQL script that you can run manually. This gives you a bit more control on the changes applied to the DB. Alternatively, you can trust EF to do it for you. You can find all the necessary ef migration commands here: http://msdn.microsoft.com/en-us/data/jj591621.aspx

EDIT The link I provided had a section called Getting a SQL Script. To do it for your migration, just run the following command:

Update-Database -Script -SourceMigration: $InitialDatabase -TargetMigration: yourmigrationname

You need to replace the "yourmigrationname" with the name of your migration and when you run this, you will get a new tab with just the SQL script. You can then choose to save it or run it.

0
votes

Since you commented on how I did it, I'll post it as an answer to clarify.

I make the changes in MSSQL server that I need. I can add columns, change column names, and even remove columns if needed.

Then in my model (.edmx) I remove the table the I just updated in MSSQL Server. Once I do that, I then right click in the open space and click Update Model from Database. I then select my table, or tables, when the "Add" tab is selected. I found that this works best as the "Refresh" never really seemed to work for me. Once you click finished, your model is now updated with the newest columns from the table you have changed.

Hopefully this helps.