Track direct database changes in Code First Model

Question

Hello,

I'm new to EF and I would like your input on the following scenario. We are building a MVC web application on top of ASP.NET Core, using MVC 6 and EF 7.

We have an existing database to take into account for this project. So at the very beginning of the project, the initial creation of the database models was done using scaffolding. The command run was:

dnx ef dbcontext scaffold "Server=localhost\\sqlexpress;Database=MyDb;Trusted_Connection=True" EntityFramework.SqlServer

Then we start programming in VS. However, due to ongoing separate projects using the same database, some changes have been made directly in T-SQL or SQL SSMS to the database. So now I am wondering how I can track & bring these changes into my VS solution without losing the work we already did. What's the best way to do that?

If I re run the scaffolding command to reverse engineer the model again, will it overwrite the changes I made in VS?

Looking forward to your advice. Let me know if you have some questions.

Thanks

Cedric

Answer 1

Hi ccornilliet,

>>So now I am wondering how I can track & bring these changes into my VS solution without losing the work we already did. What's the best way to do that?

Before you bring the changes into your solution, please migrate your changes to database by using dnx migrations, for more information, please refer to:

http://www.codeproject.com/Tips/988763/Database-Migration-in-Entity-Framework

>>If I re run the scaffolding command to reverse engineer the model again, will it overwrite the changes I made in VS?

Yes, It will overwrite the models and DbContext classes.

Best regards,

Cole Wu

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hey Cole,

If I understand correctly, this will add a migration that I could then apply to the database. This seems to be the way to go for the changes I made in the code.

But my question is more about changes made in parallel using T-SQL statements directly on to the database. How do I bring these changes into my solution since they do not exist in the c# code yet? And make sure EF is aware of them?

Hope it makes sense. Looking forward to your feedback.

Thanks

Cedric

Answer 3

>How do I bring these changes into my solution since they do not exist in the c# code yet? And make sure EF is aware of them?

Don't add migrations.

You want to use the "Database First" workflow, which was called "Code First to and Existing Database" in EF6.  In this workflow your database objects are maintained outside of your project (using SSDT or similar), and you import them periodically into your project.

You should use (or move to) this workflow any time:

1) Multiple teams share the same database

2) The database is pre-existing or will outlive your project

3) You want database professionals to help build and maintain your database objects

4) You want to use views, stored procedures, table-valued functions, triggers, etc.

So for bigger projects, you often end up using database-first eventually.  But it's pretty easy to switch workflows, which is essentially what you need to do now.

To make this work, you should separate out any additional class members you have added to your entity types (fields, properties, etc), or your DbContext (constructors, methods) into separate partial class files, and then regenerate the entities.  In short, you avoid editing any generated code files.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

Hey David,

Thanks for your reply. It makes sense.

In order to move to the "Database First" workflow, is there a way I can find & list the differences between the development DB that followed the "Code First" model and the "Live" one that is living outside the project for now and is being modified directly in SSMS for instance?

I'd like to avoid overwriting the developer changes (in the solution) by regenerating the entities. So I'd like to be sure to have all the custom DB changes made in VS listed and taken care of.

Best,

Cedric

Answer 5

Generate a model in a separate folder and then compare the files.

David

---

David http://blogs.msdn.com/b/dbrowne/