Dealing with Database Changes

by Sachin Singh

Posted on Tuesday, 05 January 2021

Tags: Dealing with database changes in database-first approach EF

Summary diagram

As we work on a project , business grows and we add new functionalities , modify the old one or fix bugs. For all this we need to make changes to our model.

With database-First workflow we always start with the database , we make changes in our database and then refresh our Model. Following are the scenarios we come across while developing a real world application.
    1. Adding a new table.
    2. Modifying an existing one. Or
    3. Deleting an existing table.

Adding a New table

In the previous article we have learnt how to create a domain model using database-First workflow. Till now, we have a database named as OrganizationDb with two tables Employee and Department , accordingly our Entity Data Model have following two Entities.

OrganizationModel.edmx diagram
OrganizationModel.edmx diagram

As you can see, at this moment our EDMX have just two Entities.

Now, if for some reason, we have to add a new table to our database, then we will have to bring those changes to our Entity Data Model as well. So, Let's add a new table called as Product to our database and bring it to our Entity Data Model.

Step 1. Add a new table to the OrganizationDb database.

   use OrganizationDb
  CREATE TABLE [dbo].[Product] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    [price] decimal(18,0) Null,
    [BrandName] varchar(50) Null,

Step 2. Go to Visual Studio and open EDMX designer. Right Click on an Empty area inside the designer and Select the option update Model from Database .

Update Model From Database
update model from database

Notice we have 3 tabs under the update wizard :
      1. Add tab
      2. Refresh tab and
      3. Delete tab

Add tab : It shows all those database objects which are currently not present (imported) in our Entity Data Model. For example , Product table is newly added table so for now it is not present in our Entity Data Model that is why it is displaying under Add tab.

Refresh tab : shows all objects that are in our Entity Data Model , that would be refresh when we click the finish button.

Delete tab : shows all objects that are in our Entity Data Model but are absent in Database.

Many new developers think , that these tabs work independently like if refresh tab is selected or open then on click finish , nothing will be added to the Entity Data Model , but this is not the case , when you click on the finish button , all existing objects in the Entity Data Model get refreshed and anything that you have deleted from the database that is currently in the Entity Data Model will be deleted.

Update Wizard
update wizard

Now, As we click Finish , the newly added table is successfully mapped to a new Entity called as Product , which can be seen in our EDMX designer.

Edmx view
Entity Data Model

we know , EDMX is actually an XML, which has three sections as Storage Model, Conceptual Model and Mappings , EF generates Entities based on the data that are there in these sections , if we want to analyze them , we use Model Browser and Table mappings.

Using Model Browser ,It can easily be seen that the newly added table is now present under Storage Model and newly generated Entity is present under Conceptual Model , while using Table Mappings, it can be seen that their mappings are now defined in the Mapping section.

Storage and Conceptual Model
Storage and Conceptual Model
product mapping
Product Mappings

Thus, updating Model after adding a new table is straight forward , we just need to perform one step that is Update model from database and that's it.

Updating a table

A table can be modified by several ways like
  • By Adding new columns (add Gender column to Employee table)
  • By Modifying an Existing table's column Name (Change DepId to DepartmentId of Employee Table)
  • By Deleting a table's Column (Delete BrandName column from Product Table)
  • By changing data type of a table's column (Change data type of Price Column of Product table from Decimal to int )
  • Open SQL Server Management Studio and Run below script for above changes on the database.

use OrganizationDB
ADD Gender varchar(50); // adding a new column
DROP COLUMN BrandName; // deleting a column
ALTER COLUMN Price int; // changing datatype name
use OrganizationDB

EXEC sp_rename 'Employee.DepId', 'DepartmentId', 'COLUMN'; // renaming a column name

so , we have made 4 changes to our OrganizationDb database and now we need to bring these changes into our Entity Data Model. The steps are exactly the same , go to visual studio , open your EDMX designer and Right Click on an Empty area and then click on Update Modal From database option and click Finish.

EDMX before changes
Before changes

we were expecting the above changes on our Entities but when we click on finish button, we get two errors and our updated Entity Data Model looks like as shown below

EDMX after Update
updated Entity data Model

From the above figure we have following Conclusions:
  1.Entity Framework doesn't see a Rename as a rename, instead, it creates a new column and doesn't delete the old one, we have changed the Name column to EmployeeName but EF has not modified the name to EmployeeName instead it simply created a new property as EmployeeName.
  2. Entity Framework doesn't delete a property
  3. Entity Framework maps a new column to a new property without any issue.
  4.Entity Framework changes the data type without any issue as long as the conversion is safe. In our case, we changed decimal to int, it was not a safe conversion so EF has thrown an error stating conversion is not compatible.

Thus, in case of modifying a column, deleting a column, or changing its data type, we have to take some manual action, we manually need to double click on the particular property and delete them which we don't need anymore.

To solve the data type conversion error we need to open the column property and fix it. so , Right Click over the property's name and select property and change the property type to int from decimal.

Column's property
property window
property windows

Now to validate the mappings, Right click on an empty area under EDMX designer and select validate

validate model
validate model

Deleting a table

Let's delete the product table and update our Entity data model.

step 1. Run below SQL query to delete the Product table.

 use OrganizationDB
 DROP TABLE Product; 

Step 2. Go to Visual studio and open your EDMX designer. Right Click on an Empty area and select the option update model from database . You will get the below error.

Error after updating EDMX

so , we need to manually delete the table that we don't need ,so select the product table and simply delete it.

Now to validate the mappings Right click on an empty area under EDMX designer and select validate

validate model
validate model

following diagram completely summarize the article.

Summary diagram