Can I use MODEL-FIRST in EF5 without losing data in the database?

Question

I'm curious about the first approach to the model. I want to create a new database using a modeler in VS2012. New features of the fashion designer, such as coloring and separation of sections of models, are wonderful. We hope that it will be advisable for the model designer to re-create a new database.

I would like to follow these steps ...

• Using the model builder, visually design and click the model to create the source database and table

• add data to table

• make changes to the table in the model designer (for example, add a field)

• click database changes (i.e. update the database)

• DON'T LOSE MY DATA FROM STEP 2. Also, just to eliminate any confusion ... I mentioned that I DO NOT WANT TO LOSE DATA?

Please tell me this obvious need (i.e. the need to develop tables and their fields without losing data, starting from scratch) was not lost on the FIVE EF iteration.

This page on EF ( http://msdn.microsoft.com/en-us/data/ee712907.aspx ) suggests that the developer has the same choice between the first coding and the first simulation. For me, the embedded video on the page creates a similar impression.

It would be nice if there was a simple menu option or even better, but only a way to set "automatic pressing on DB" when changing the model. Thus, each time you make changes and press the SAVE button, the "Update Database?" Dialog box appears.

I see that using the code there is a migration option first. I can't seem to find the same for the model. And I don’t understand why this was impossible ... because the code that I would write in code-first really exists - it was created when generating the code of the first model.

I hold my fingers in the hope that someone will have a simple solution, maybe something that I just missed, and all this incoherent / venting is in vain. :-)

Answer 1

You really need to use the code first if you want to change your database when changing the model. Even then, this is not some kind of magical automated process, but you will have to make the script change.

With a model, it is first best to create a new database each time and create a script change (DDL) using a tool such as Redgate SQL Compare or Visual Studio Sql Server Database Project .

I would like to add that it is almost impossible to synchronize the database automatically with the model. Some changes require manual intervention, for example. deleting a field and adding another field cannot be distinguished from renaming / renaming a field. Some changes can be easily implemented in the model, but this will require rebuilding the script table on Sql Server (for example, changing the order of fields) or a combination of changed content and structure (for example, creating a non-null field, adding a foreign key).

Answer 2

At the moment, the only thing to do:

• Copy the database file ... (backup)
• Allow EF to recreate the database according to the model
• In the table, copy your entries from the backup to the new db.

This is not so simple as you need to copy the insert in a specific order due to relationships, and it will only be useful for minor changes, such as adding columns and new tables or deleting scalar columns or deleting tables.

But I am sure that this is the beginning of a correct approach to solving the problem, which can later be automated by writing a more universal application for migration between two databases that have the same names and relations in the table.

Deeper problems begin when relationships do not match / table names / column names are changed.