Partager via

Update Model From DB

  • Article

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

Comments

  • Anonymous
    April 01, 2008
    Oh, good, I was literally about to update a model. Could I maybe have that right now? ;-) Looking forward to the improvements.

  • Anonymous
    April 01, 2008
    Sounds fantastic, i hope that we will se this in a CTP very soon, as we really need these improvements. If you need somebody to try it out, let me know ;)

  • Anonymous
    April 01, 2008
    Ceux qui ont un peu joué avec le designer le savent, c'est encore une CTP. La mise à jour du modèle à

  • Anonymous
    April 01, 2008
    Any ETA of next release? we're looking forward for this improvement...

  • Anonymous
    April 02, 2008
    Thank you guys for the improvements! I need that baby right now! :D A quick question: What about complex type support?

  • Anonymous
    April 03, 2008
    Noam Ben-Ami writes about the improvements we will see in how the designer updates models in the next

  • Anonymous
    April 03, 2008
    Complex type support did not make it into v1.

  • Anonymous
    April 03, 2008
    I did not realize actually writing code became a hated chore; only tools which remove us from that horrible code will help. No thanks, folks, I got into this field to write code; not be a drag-n-drop monkey. I wish you all luck since you know what monkeys are paid, right? Peanuts and bananas; not six-figure salaries. Sheesh... if you hate writing code please get out of the field.

  • Anonymous
    April 06, 2008
    Nice but from a software development view I would prefer putting the effort in (re)generating the database from the model ;)

  • Anonymous
    April 06, 2008
    We hear you, and we're absolutely putting effort there!

  • Anonymous
    April 06, 2008
    Good work, can’t wait! In addition to desperately wanting the next version (or at least a hint of when it might appear, even if it might change), I would like to see:

  1. More method/property name consistency with Linq to SQL (eg. SubmitChanges() not SaveChanges(), DataContext.Table.InsertOnSubmit() not DataContext.AddToTable() etc)
  2. Support for sql_variant with the ‘object’ type.
  3. Support for inserting multiple rows like Linq to Sql (DataContext.Table.InsertAllOnSubmit(MyObject[] { row1, row2, row3 })) Thanks!

  • Anonymous
    April 21, 2008
    The comment has been removed

  • Anonymous
    April 25, 2008
    It would be much nicer if the Update Model from Database dialog were to create a copy of the current model, update/add all available tables and then let you preview the changes associated with the individual checkboxes on the Add/Refresh tabs. While I like the fact that you don't delete from the model, I'm sure that if you can produce a working tool all the manual maintenance will quickly become a major nuisance. Previewing changes sounds like a much nicer user interaction model, and if everything fails there's always Undo in Visual Studio.

  • Anonymous
    May 02, 2008
    During the past month, Danny Simmons let us all officially know that SP1 of VS 2008/.NET Framework 3.5

  • Anonymous
    July 22, 2008
    I propose to explain the reason behind some of the oddities of the “Update Model from Database” functionality

  • Anonymous
    July 23, 2008
    Based on the from ADO.NET team blog post and my experiment, changing the data type in the phyiscal table

  • Anonymous
    July 23, 2008
    Based on the from ADO.NET team blog post and my experiment, changing the data type in the phyiscal table

  • Anonymous
    July 29, 2008
    When I select "Update Model from DB" it shows me the pop up windows with "Add" and "Update" tables, views, SP option. When I select "Update" it throws an message: "You cannot select or deselect....." Hence I end up with all my DB tables again in my EDMX. If I want to update and get only 1/2 DBtables I still end up getting all tables again. Is there any way out?????

  • Anonymous
    August 15, 2008
    Is there a way to update only the SSDL and not the Model? I only want a simple "refresh tables" function without adding new columns to my model. Not all columns in database are needed in these models (e.g for implementing inheritence)

  • Anonymous
    October 20, 2008
    Update Model from Database 是EDM 设计器的一个重要改进功能，支持增加新的数据库表和列，Model能够正确处理类型（Type）和属性（Property）的改名，能够检测继承和映射的改变。这里，我们简要介绍这一功能的使用及其限制。上一篇文章《Entity Framework – Update Model From Database, Part 1》

  • Anonymous
    January 01, 2009
    I've been watching your video and now found this. It is the same. When you use Update Model, it has checkboxes which open up sub checkboxes for tables, views, sp's. However, I am using VS2008 with SP1 and when I do the same thing, it simply has one checkbox each for the tables, views, stored procedures, but does not open a subtree with all of my tables, views and sp's from which to select. Why is that? Did they take that option out? Or do I have an old version or what is going on? I just recently bought my 2008 a few months ago and have SP1. I just cannot select my tables, etc. using the update model. Also, when I update the model based on changes in the tables sometimes it works, and other times it doesn't. And I end up having to delete the entire model and recreate it. So far, I am really disappointed in the Entity model.

  1. the model doesn't update as it should without deleting and rebuilding. So forget trying to customize.
  2. the model doesn't support stored procedures that are not insert/update/delete. Even though you can Create Function Import and it allows you to have a return type of None, forget ever trying to use it. It doesn't allow you to use it!! So why tease us with it? I have checked all over the forums and blogs and the consensus is, unless it returns the exact structure of your entities, forget using it.
  3. if I combine linq to entities with an ado.net stored procedure (to use my unsupported stored procedure from #2), the changes made within my stored procedure simply do not get propagated to my entities model and somehow when I use linq to entities to pull from my tables, they are still using the old data prior to my calling the stored procedure. If I use ado.net with a sqlcommand, it will pull the latest. The changes are not flushed to the entity model. Which brings me to #4.
  4. I can't get any help on using this. Rarely does anyone write about how to do real world things such as using the Create Function Import to run a stored procedure that returns nothing. Has anyone done it? I don't think so. Because numerous have told me it can't be done. And when I ask questions in the asp.net forum, I just don't get answers. I thought I'd "move ahead" with the latest technologies, but the technology doesn't seem to be well supported, well discussed and seems to be very incomplete. And using this has put me weeks behind in development. I'm not the only frustrated user, just check the forums.
  5. You can find all kinds of support for dynamic data, silverlight, and MVC, but where is the support for ADO.NET Linq to Entities? There isn't even an asp.net forum dedicated to it. I'm simply tired of being Microsoft's unpaid debugger.

  • Anonymous
    December 15, 2009
    The comment has been removed

  • Anonymous
    January 07, 2010
    Unfortunately, I have to echo Moon's observations, especially re #1 which is very frustrating. Support seems thin echoing #4.

  • Anonymous
    March 09, 2010
    Unfortunately, I have the same problems as Moon's #1 remarks. For update my model I must delete, and recreate it. Very frustrating. Linq to Entities (with MySQL) & VisualStudio2010 RC

  • Anonymous
    August 28, 2010
    The comment has been removed