If you’re using Entity Framework Core with the Database First approach you may be looking for a suggested development workflow in terms of how changes to the DB can be managed in a team environment.
In this post I’ll outline the major elements of the workflow I tend to use on most EF projects. First, here’s two fundamental ground-rules to using this workflow…
DB is KING… C# models and DbContext should never be customised DIRECTLY
In a true Database first approach (as opposed to a one time scaffold from an existing DB) the DB is the owner or start point for the C# models which the Scaffold-DbContext command will create. This means your C# models should be completely rebuildable after any DB changes so don’t customise the scaffolded models. The same applies for your DbContext class, don’t customise this directly.
To add custom code to your models use partial classes…
To add custom code to your DbContext, create a new one and inherit from the generated one…
Database objects are stored in a SQL Server DB Project and must be source controlled
Aside from never customising generated code a second prerequisite or ground rule is that the database must be source controlled. We must store the definition of the DB in source control so other developers can pull it down and synch to their local DBs.
Assuming we are using SQL Server we can create a representation of our DB in a SQL Server Database Project in Visual Studio and add it to our solution….
Suggested development workflow when DB schema changes are needed
OK assuming we know not to ever change auto-generated EF Core code and that our database must be source controlled lets look at the day to day flow…
– 1 –
Developer gets latest and uses the DB project to push any changes into their local DB
Before the developer is about to start on some new work, they need to make sure they’ve got the latest code and database project downloaded from source control.
Using the Visual Studio schema comparison tool they then compare the DB project to their local DB to see if any other developers made changes. If they have some differences will be listed in the comparison results and the developer can then use the tool to push the updates (no scripts needed) to their local DB.
I prefer to, but you don’t have to check the schema comparison files in but it’s useful to have each developer at least have them saved locally so they can just double click them to run comparisons.
Two comparison files will allow a developer to synch from their DB to the project and vice versa….
– 2 –
Developer adds a new column to a table in their local DB
Let’s say we want to add a new column for website address to the customer table so we run the below…
ALTER TABLE [SalesLT].[Customer] ADD WebsiteAddress nvarchar(500) NULL;
– 3 –
Re-scaffold from the developers local DB and include the -force parameter
The DB has been updated so we now need to update the C# models to match so we run something similar to below in the Package Manager Console…
Scaffold-DbContext name="AdventureWorks" Microsoft.EntityFrameworkCore.SqlServer -force -outputDir Models -contextdir Context -Context EFContext -UseDatabaseName
or we can use the .NET CLI and wrap the scaffold command in a bat file.
The connection string is referenced from a named connection in appsettings.json and will be pointing to the developers local DB. At this point we can’t scaffold directly from a DB project but this functionality is supported in EF Core Power Tools if you’d like to check it out.
Note the inclusion of the -force flag which tells Entity Framework Core to overwrite existing files. In this case the previously generated Customer.cs file will be re-created and will now include a new string property called WebsiteAddress. The context file will also be re-created and will now include a mapping corresponding to WebsiteAddress.
Our pending items will look similar to…
– 4 –
Developer codes as normal and repeats steps 2-4 until ready to check in.
At this point the DB and the C# models are in synch so the developer can just continue to develop as normal. When they are ready to check in they move to step 5…
– 5 –
Developer runs comparison tool in Visual Studio to compare their local DB to the DB project
The developer is finished the task but if they check in only the C# code the next developer to do a ‘Get Latest Version’ command in Visual Studio will have problems. We need to check in the C# model changes and corresponding DB changes together…
To update the DB project we can again use the comparison tool in Visual Studio. This time the source will be the developers local DB and the target will be the database project on the developers local machine.
Note the comparison tool uses the locally downloaded version of the project. It is not possible to compare directly against the latest version that’s in source control. This means it is very important to have the latest project downloaded locally.
After running the comparison tool the output will look a bit look the below image. Click the image for a larger view in a new window.
We can see the tool has found one change corresponding to the WebsiteAddress column added earlier. To push this change into the project we need to select the checkbox in the middle and click Update.
After the update completes the database project will have been updated and the pending changes window will now look similar to…
Note… if we added a new object as opposed to editing an existing one the DB Project file itself would also show in the pending items.
You don’t have to do this but before checking in I like to…
1) re-run the comparison to make sure that after updating the DB project there are no differences left (in this case) or only differences whose corresponding C# model code will not be checked in either.
2) Compare the local version of changed objects to the latest in source control just to make sure I can reconcile every change…
– 6 –
Check in the database project changes and model/DbContext changes TOGETHER
All that’s left is to check-in….
Deploying changes to promoted environments such as DEV, UAT or PROD
How you deploy to forward environments will vary depending on your processes but for the DB part it will likely involve using the comparison tool to compare the DB project against the target DB and then building up deployment scripts.
I usually have comparison files similar to below saved and checked in to the DB project…
Because the target is a database (and not a DB project) the generate scripts button will be enabled…
The script created should be used only as an aid and needs to be manually reviewed before execution.
Note…. I recommend always using the DB project and not an actual DB as your single source of truth when building up rollout scripts as this forces developers to check-in their DB changes otherwise their functionality won’t work on DEV, UAT and PROD.
Checking in DB changes is very important as DBs are rebuilt frequently, developers do stupid things to them, old redundant objects are often not dropped etc. Having your objects checked in means you can always rollback to a previous version and understand why or who made a particular change to an object.
I’ve used the above database first workflow in teams of 5-20 developers regularly over the last few years without major issues. The issues that do arise are usually due to developers not following the basics such as getting the latest code before starting on new tasks and checking in generated EF code and corresponding DB object changes together.
Let me know what you think…