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.
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
go
CREATE TABLE [dbo].[Product] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] VARCHAR (50) NULL,
[price] decimal(18,0) Null,
[BrandName] varchar(50) Null,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
);
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 .
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.
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.
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.
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
go
ALTER TABLE Employee
ADD Gender varchar(50); // adding a new column
ALTER TABLE Product
DROP COLUMN BrandName; // deleting a column
ALTER TABLE Product
ALTER COLUMN Price int; // changing datatype name
go
use OrganizationDB
go
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.
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
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.
Now to validate the mappings, Right click on an empty area under EDMX designer and select validate
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
go
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.
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
following diagram completely summarize the article.