locked
Update Model From Database Problem RRS feed

  • Question

  • For a while I have been suspecting that "Update Model from Database" feature in edmx diagram is not working as I expected. So, I decided to test it. I created a simple database with a single table from which I created the edmx file. Then I added a column to the table and used "Update Model from Database" to reflect the change in the model. That step worked as expected and the model showed the additional column. Then I went back to the database and removed that column and tried again to Update the Model using the same steps as before. This time the model did not reflected the change. I tried different things to force the change but nothing seems to be working.
    Finally I tried a drastic measure and deleted the edmx file and rectreated it from scratch. during this step I discovered another issue. I could not reuse the same entity name because the system complained that the name is already in use. This issue represent a compound problem, since now all code refrencing the entity context need to be renamed. Not sure if this issue is a bug or by design. If anyone has an explaination I would greatly appriciate it. The steps I took above are pretty easy to recreate.
    Thanks,
    Zev
    Tuesday, November 17, 2009 5:45 PM

Answers

  • Hi Zev,

     

    If I understand the scenario here clearly, you delete the first and last name columns in the Purchase table and add a customer id column.  Then you create a new Customer table to store the customer information.  Of course, I think the customer id column in the Purchase table should be a foreign key column pointed to the Customer table. 

     

    If that is the case, you can following these steps:

    1.      Right click on the EDM designer and select “Update Model from Database…”.

    2.      Under the “Add” tab, check the new data table “Customer” and press the “Finish”.

    3.      Then we just need to delete the first and last name columns in the Purchase entity in the EDM designer.

    4.      Build the project and everything should be fine.

     

    Zev, I’d like to share more ideas on the problem in this thread.  As I have said, “Update Model from Database…” will update the SSDL part of the EDM. However, the CSDL part should be managed by the user.  Because the CSDL can be different from the data structure in the SSDL (what we see in the database).   We have some samples in the project CSEFEntityDataModel and VBEFEntityDataModel of All-In-One Code Framework.    In the samples, we demo various ways to work with EDM including some common associations, table merging, table splitting, and some common entities inheritance.  In the table merging, table splitting and inheritance parts, we can see the CSDL are quite different from the database structure.   All the modifications are customized by the user, so I think it is a good choice to let the user manage the CSDL part. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by dzFactor Wednesday, November 25, 2009 10:01 PM
    Wednesday, November 25, 2009 5:44 AM
  • Thank  you Lingzhi Sun,

    If I understand you now, what you are saying that you need to make the changes in the database, then mimic those changes in the dbml designer ( those items that do not get updated that is).
    I tried it and it worked as you said.

    Thanks
    • Marked as answer by dzFactor Wednesday, November 25, 2009 10:06 PM
    Wednesday, November 25, 2009 10:06 PM

All replies

  • Good morning Zev,

     

    I believe the behavior is by design.   When we update the model, what first gets refreshed are the EDM’s Store Metadata Schema (SSDL), which stores the database information.   So when you added a new column and update the model, the new column is added into the SSDL, EF detects the modification and finds there is no corresponding property in the entity (Conceptual Schema), so it automatically creates a new property.  

     

    If we then delete the new column at the database side and refresh the EDM, EF will update the SSDL as well.  However, EF cannot know whether the certain property in the entity should be deleted or it is just added by the user.   We will get a compiler error: “Error 11009: Property ‘NewColumn’ is not mapped.”   So if the property is not necessary any more, we can just delete it. 

     

     

    As for the same entity name, the reason that we cannot create it again is that we have already contains such an entity name in our app.config or web.config.   By default, .NET application will save your entity name, metadata information, connection string, and EF provider in the config files.   So before creating a new EDM with the same name, we can just delete the connection string element in the config file. 

    =====================================================================================
    <configuration>

      <connectionStrings>

        <add name="TestDBEntities" connectionString="" providerName="System.Data.EntityClient" />

      </connectionStrings>

    </configuration>
    =====================================================================================

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 18, 2009 1:35 AM
  • Thanks Sun for your detail explaination on the innerts working of the data model mapping. However, I am still not clear why the system does not simply rewrite the generated code, or at least give you an option to do so. If the user decide to add property to a class in the model, it would be done OUTSIDE the generated code. In other words, why does the system care if the user add property to the model, assuming it is done outside the generated code. My expectation, and perhaps I am wrong, was that the dbml should map whatever the database model reflect and not try to outguess the intent of the user.
    Friday, November 20, 2009 5:40 PM
  • The first issue is indeed by design: The update model wizard has a philosophy that the user owns the model and that the wizard should not run about deleting things from that model.

    The second issue I do not understand: If you delete the edmx file and the generated code, the system should not complain about duplicate names. It would be helpful to see the exact error.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, November 20, 2009 7:42 PM
  • Hi, I am still puzzled by the reasoning behind the answers. When one designs a "wizard", one designed it with a task in mind. In our case, to map database schema to a data model classes. It is not in the nature of a "wizard" to have a philosophy. If the user wishes to remove a column or rearrange his database in whatever fashion he chooses, it is the task of the "wizard" to comply and reflect those changes in the generated code. Yes, it would be nice to warn the user that a column is being removed, and allow the user to cancel or confirm. But I have never heard of a "wizard" designed to assume it knows better what the user's needs are. I wish someone with a better answer stand up and clear this muddy water for me, because the answers that came forth so far sounds like a shot from the heap to say the least. Thanks, Zev.
    Monday, November 23, 2009 5:09 PM
  • Hi Zev,

     

    I think Noam means that the design goal here is to let the user manage the CSDL part of the EDM.  When updating the SSDL from the database, we can add new properties to match up the database modification.  However, it is not recommended to delete or modify the current user’s model properties because the user might have customize them and if we change the user’s settings, it may not be repairable.  So I think it is a good choice to throw an error and let the user make the final decision.  

     

    Of course, Zev, you are welcome to share your ideas and suggestions on this problem.  Because of the suggestions of our customers like you, Microsoft is marking our products better and better.   I think your suggestion is really a good idea to give a warning to the user that a column is removed or modified.   I strongly recommend you to send such a suggestion at Microsoft Connect website.  I really appreciate if you can share the suggestion link here to benefit more community members.  Thank you very much, Zev. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 24, 2009 12:54 AM
  • Thank you Lingzhi,

    Sorry for what probably sounds like frostration on my part. I simply assumed that updating the model meant just that, no string attached.
    I will follow your suggestion about sending my recommendations, but I still did not get an answer to a real problem in using "Update Model from Database".

    I still need some answers on how to go about updating my model in a case I need to rearange my database.

    Here is a scenerio that demonstrate what I mean:

    Let's say I have a table with a couple of columns that now I realized may be more appropriate to move to a new table with some additonal columns and leave a reference behind to point to that row. For the purpose of our discussion, let say a user first and last name is stored in Purchase table, and now I want to store his address, phone, hair color and pens size. Naturally, I will need a new Customer table, leaving behind a reference to that customer id in the Purchase table.

    This kind of things happen all the time when your application evolves and require more data. How would you suggest I would update my model, now that I have deleted the first and last name columns, replacing it with an id column, and adding a new table to contain those columns. Please detail the steps if you can.

    Thanks,

    Zev.

    Tuesday, November 24, 2009 7:39 PM
  • Hi Zev,

     

    If I understand the scenario here clearly, you delete the first and last name columns in the Purchase table and add a customer id column.  Then you create a new Customer table to store the customer information.  Of course, I think the customer id column in the Purchase table should be a foreign key column pointed to the Customer table. 

     

    If that is the case, you can following these steps:

    1.      Right click on the EDM designer and select “Update Model from Database…”.

    2.      Under the “Add” tab, check the new data table “Customer” and press the “Finish”.

    3.      Then we just need to delete the first and last name columns in the Purchase entity in the EDM designer.

    4.      Build the project and everything should be fine.

     

    Zev, I’d like to share more ideas on the problem in this thread.  As I have said, “Update Model from Database…” will update the SSDL part of the EDM. However, the CSDL part should be managed by the user.  Because the CSDL can be different from the data structure in the SSDL (what we see in the database).   We have some samples in the project CSEFEntityDataModel and VBEFEntityDataModel of All-In-One Code Framework.    In the samples, we demo various ways to work with EDM including some common associations, table merging, table splitting, and some common entities inheritance.  In the table merging, table splitting and inheritance parts, we can see the CSDL are quite different from the database structure.   All the modifications are customized by the user, so I think it is a good choice to let the user manage the CSDL part. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by dzFactor Wednesday, November 25, 2009 10:01 PM
    Wednesday, November 25, 2009 5:44 AM
  • Thank  you Lingzhi Sun,

    If I understand you now, what you are saying that you need to make the changes in the database, then mimic those changes in the dbml designer ( those items that do not get updated that is).
    I tried it and it worked as you said.

    Thanks
    • Marked as answer by dzFactor Wednesday, November 25, 2009 10:06 PM
    Wednesday, November 25, 2009 10:06 PM
  • The first issue is indeed by design: The update model wizard has a philosophy that the user owns the model and that the wizard should not run about deleting things from that model.

    The second issue I do not understand: If you delete the edmx file and the generated code, the system should not complain about duplicate names. It would be helpful to see the exact error.
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Hello, sorry if i don't write englis well.
    About first issue why don't let the user decide how the wizard should work. One option would be: update the model exactly the same as the database structure(that's the option i will always choose). And the other option would: Mr. Wizard don't delete my changes and work the way you actually do.
    Thank you.

    Other thing i want to comment is
    When i have a not null column on the database and created a model. If later i decide to change that column to allow null values and when updating the model it doesn't reflect the change, so if i manually set the property Nullable to true, everything seems to be ok. But the problem is when i try to persist changes to the database. An error message says me that the column doesn't accept null values.

    These problems about updating the model forced me to create little models with few tables, so if i change the database structure it won't be difficult to me to delete the model and recreate it again. I have no other option (until now i have 100 tables aprox.).

    Please give me alternatives to work without manually change models (navigating through xml file). Thank you very much.
    Even with these problems i like EF and i will continue using it.

    Thanks

    José Flores.

    Friday, November 27, 2009 1:25 PM
  • Its a design philosphy that has caused me many hours of pain and heartache.

    It also has other very annoying 'philosophies' aswell. If you delete a column that you didnt think you needed in your CSDL and then realise you actually need it, you'd think there would be a way to do it. NO. CSDL has recorded somewhere that you wanted it deleted so now your only hope is to delete the whole table and re-add!

    While I appreciate the thought process behind approach, 'CSDL is owned by user', the real-world usage (at least in my experience) is that no-one uses CSDL to create models! It is too clunky, its slow, confusing, Model Browser.. yuk. Stored Procedure mapper, sorry Function Import arghh, which comittee came up with this name!!!! 

    People create partial classes, project annoymous (or DTO) types or use views. They do not try to build a HUGE single point of failure (this flaky tool) into their project. It would be sheer lunacy to try that.

    I would urge MS to review this tool, make it faster, make it do what most people want it to do (just map the DB or at the very least as a user configurable option) and not force us to wage a holy war with this tool everytime we want to update our ORM.

    Tom

    Thursday, January 19, 2012 12:04 PM