The “Update Model from Database” feature in the CTP2 of the EDM designer is greatly improved in the next designer release. It now supports iterative development scenarios by adding new database tables and columns to the schema, correctly handling type and property renaming in the model, and detecting certain inheritance and mapping changes. We’ll explore these scenarios in this blog post, as well as some of the usability changes that we have made. We’ll also talk about some of the limitations of the current implementation and discuss how we see this feature evolving.
To begin, we will use the Northwind database and bring in the Products table:
Next, we rename “Products” to “Product” and rerun Update Model from Database – in the next release, this option will be available from the designer context menu, here is a preview:
Note that we’ve also added shortcuts to both the table mapping and stored procedure mapping UIs.
Update Model from Database now looks like this:
We’ve moved the Refresh and Delete sections to make the interface more familiar.
To clarify the behavior of these tabs – the objects they show are all database objects:
- Add shows objects that are in the database but not in the model.
- Refresh shows objects that are both in the database and in the model.
- Delete shows objects that are in the model but not in the database.
Because the wizard always regenerates the SSDL – any changes made to the SSDL will be lost on refresh - the update and refresh lists are read-only. We are looking at solutions that will enable merging of changes in the future.
We add the Orders, Order_Details, and Suppliers tables to our model by checking their checkboxes and clicking on “Finish”:
The takeaway here is that the wizard now correctly handles renames. However, we’ll see a number of validation errors, the first of which is:
“Problem in Mapping Fragment(s) starting at line(s) (110, 176): Non-primary-key column(s) [SupplierID] being mapped in both fragments to different conceptual side properties - data inconsistency is possible because the corresponding conceptual side properties can be independently modified.”
The Entity Framework is telling us that column SupplierID in table Product is mapped twice. What happened was that the initial import, which included only one table, did not create the association, and so the SupplierID field was surfaced as a property. If we look at the mapping for the association between Product and Suppliers we see that that association maps to the SupplierID column:
This is a scenario where the Update Model Wizard will not help us, as part of its policy is to avoid deleting any part of your model. So we delete the SupplierID property from the Product type, and the model validates.
For our next step, we will make a some changes to the database:
- Add a new bit column called “IsCurrent” to the Suppliers table.
- Delete the “ContactTitle” column from the Suppliers table.
Once those changes are made, we update the model again, and this is what the Suppliers type looks like:
We also see a validation error:
“Property ContactTitle is not mapped or used in a condition.”
The wizard has added the new property to our type – another requested behavior. But, it has not deleted the ContactTitle property, because of the policy of not deleting elements from your model. So, we delete the ContactTitle property manually.
The next set of database changes will showcase the new mapping analysis feature in the wizard:
- Add a table for new kind of supplier, call it PremiumSuppliers. Give it two columns, as shown below.
- Add a SupplierContract table to our database to hold a large contract document and additional metadata. Give it three columns as shown below.
- Add PK to PK constraints between these two tables and the Suppliers table, with the primary key table being the Suppliers table.
Update the model again, this time adding the two new tables. The relevant part of the model should look like this:
Now – we will copy and paste the properties from SupplierContracts to Suppliers and delete the SupplierContracts type. We will then go into the table mapping view for Suppliers and map it to the SupplierContracts table by clicking on the “<Add a Table or View>” cell and selecting “SupplierContracts”. The mapping view will look like this:
I have highlighted the one place where you will manually need to make a change: The SupplierContractID column must be mapped to the ID of the Suppliers type in order for the Entity Framework to understand how to construct the appropriate database queries.
Next, we delete the association between PremiumSuppliers and Suppliers and make PremiumSuppliers inherit from Suppliers. We then delete the PremiumSupplierID property from PremiumSuppliers since the key is now inherited from Supplier, and we fix the mappings as shown:
The relevant part of our model now looks like this:
Finally, we update the model again…and the model does not change! A bit anticlimactic after all this work, but consider: The wizard has analyzed the mappings and noticed that the two PK-to-PK associations have been replaced: One with an inheritance relationship, the other by mapping one type to two tables. Using this information, it avoids bringing these associations back into your model.
A few final notes about the wizard’s limitations:
- It does not update the types of properties when the corresponding database columns change.
- It does not “resurrect” entity types – once you have deleted a type, the only way to get it back is to recreate it manually, or delete the corresponding SSDL by hand in the XML editor.
- It will also not “resurrect” properties.
- If you change the keys that define your type, all current associations lose their identity and new associations will be brought in – you will need to delete old associations.
- It cannot detect database object renaming – renames will show up as the deletion of an old object and the addition of a new one.
These limitations aside, we think you will find the new functionality both useful and more usable. We look forward to your feedback.
Noam Ben-Ami,
Program Manager, ADO.NET Entity Framework Tools