How do you update an edmx file with database changes?

Question

I have an edmx file and I changed a table in my database. I know that there is an "Update Model from database" wizard, however in many cases this is useless.

For example if I change a field from non null to nullable or if I remove fields the update model does not reflect the changes. I have had to remove the entity and add it back in to get the changes to appear in my model.

Per the following question: How do I propagate database changes to my .edmx file?

One of the answers seems to say the same thing, that you need to remove the entity and add it back in.

Is this the definitive answer or is there a better way to do this?

possible duplicate of [Updating your edmx to reflect changes made in your db (.net linq-to-entities)](http://stackoverflow.com/questions/690248/updating-your-edmx-to-reflect-changes-made-in-your-db-net-linq-to-entities) — Tim Abell, Dec 05 '14 at 10:52

score 65 · Answer 1 · edited Nov 14 '18 at 04:51

Updating an EDMX the safe way:

As you have found, the update from database does not always change existing properties correctly.

From our day-to-day use of EDMX updating (100s of updates over 24 months), I would recommend the following sequence for updating an EDMX.

Delete existing model and then update:

Open the EDMX designer
Ctrl-A to select all
Delete key to delete all models in the designer
IMPORTANT: Do not save the EDMX at this point if you are using TFS for source control!*
Now right-click and select "Update Model from Database" to recreate the entire model again.
Rebuild project to propagate changes

This will obviously lose any manual tweaks you have made to the model, but manual tweaks are to be avoided if possible. This makes the entire process reproducible at any time (which is a good thing).

Important notes:

If you have auto-save turned on in Visual Studio, you need to select the update (step 5 above), quickly to avoid an auto-save saving everything.
If you are using TFS for source control, and happen to save the EDMX after emptying it, TFS will mark all the generated files as "deleted" and updating the EDMX again can result in disconnected files that are not in source control!.
This process will not update any stored procedures. Further, I have found that a refresh of an EDMX will also not update stored procedures where just the return type has changed (still current as of EF 6.1.1).

Additional Recommendation:

Keep your EDMX in a separate library. This also becomes a great place to add additional TT files and partial classes (e.g. to extend function of EDMX models). I also place any extension methods for the database context in this library. The migration files get generated in the library too keeping it all nicely contained.

Update April 2015

The latest Release 4 of Visual Studio 2013 appears to have resolved a lot of the TFS issues. We now see Visual Studio checkout generated files, then revert them if they are unchanged. The above steps still appear to be the safest approach.

Update September 2015

Using latest VS2013 Release 5, we still have issues if a save occurs during EDMX update. You can still wind up in a state where pending deletes causes your tt files to be removed from source control during the update. The secret is to update fast between steps 4 and 5! :)

Is there a good way to always set StoreGeneratedPattern on my primary key to Identity after Update Model from Database, because or our Ids are defaulted to newid() on sql server. — OKEEngine, Feb 24 '16 at 23:21
@JerryLiang: As mentioned *all* custom tweaks to the EDMX are lost doing this. You either have to work with the default SQL settings, or repeat your custom changes after each EDMX update. The default `newid()` should only apply to newly inserted records, so I am not sure what difference `StoreGeneratedPattern = Identity` actually makes. Perhaps you can clarify? — iCollect.it Ltd, Feb 25 '16 at 10:08
Is there a way to add exporting of the structure of the database to a sql file, when updating the edmx? — Bakudan, Apr 27 '16 at 12:01
@Bakudan: It is not designed to go back and forth, but theoretically you could generate a *code-first* database from the edmx *generated classes*. You normally need to create a database creation script with a tool like SQL Server Management Studio. — iCollect.it Ltd, Apr 27 '16 at 16:23
@Gone Coding Thanks for sharing this detailed step by step. For those like me who read Gone Coding's answer too late and succeeded in removing/disconnecting file(s) from the source control, what I did was to do an `Undo Pending Changes` on the `edmx` file (preferably the entire data layer). Then I did a `Get Latest` version. What happened is my `tt` file(s) is now back in SC. Then do the steps mentioned above. HTH. — Annie Lagang, Jul 30 '17 at 23:10
@AnnieLagang: Glad it helped. We have long since switch to using GIT repositories as TFS got way too slow for us (local or cloud-based). Cheers — iCollect.it Ltd, Aug 03 '17 at 16:08

score 46 · Accepted Answer · answered Mar 14 '12 at 20:39

An important first step is to understand exactly what happens when you use the update model wizard.

From the MSDN Library:

The ADO.NET Entity Data Model Designer (Entity Designer) uses the Update Model Wizard to update an .edmx file from changes made to the database. The Update Model Wizard overwrites the storage model as part of this process. The Update Model Wizard also makes some changes to the conceptual model and mappings, but it only makes these changes when objects are added to the database. For example, new entity types are added to the conceptual model when tables are added to the database, and new properties are added to entity types when columns are added to a table. For details about what changes are made to the .edmx file, see Changes Made to an .edmx File by the Update Model Wizard.

When you updated the database using the update model wizard, it updated the storage model in the .edmx file and not the conceptual model. When changes are made to the definition of existing objects, only the storage model is updated; the conceptual model is not updated. For a complete description of changes that are made by the update model wizard, please see the "Changes Made to an .edmx File by the Update Model Wizard" link above.

Here are some options on how to update objects that are not updated by the update model wizard (based on your scenario where a the column definition was altered):

Use the update model wizard (to update the storage model), open the .edmx file using the designer (default), find the desired scalar property and edit the desired properties in the Properties windows.
Use the update model wizard (to update the storage model), open the .edmx file using the XML editor, find the desired property in the CSDL (conceptual model) section and change the desired attributes. This is basically the same as option 1, but you're editing the XML directly (a find and replace might be useful here).
From the Model Browser, delete the desired entity from the Entity Types section of the conceptual model and the desired table from the Tables / Views section of the storage model. Then use the update model wizard to add it back.

The best option would depend on the given scenario. For example, if you just altered the definition of one column, then option 1 is likely you best choice. If you altered the definition of a number of columns in a single table, then option 3 might be your best choice. If you altered a column that is used across a number of tables (such as a primary / foreign key), then editing the .edmx XML directly might be your best option.

Thanks for this...Learned how it works... Most of the times, deleting the table from EDMX and then updating from Database worked for me.. Now I know why — A3006, Oct 19 '16 at 11:17
what "conceptual model"? what is that? other than the concept of a model? — Gerry, Mar 10 '21 at 23:17

score 2 · Answer 3 · answered Nov 23 '16 at 15:37

Consider I have added a new column(c1) to my existing table. Then to update the same in my existing Entity Model, I would do the following.

I will open the .edmx file in notepad ++.

I will add the property c1 to .edmx file where ever necessary. For example I would add c1 node below every c0 node.

    <EntityType Name="table">
      <Key>
        <PropertyRef Name="Id" />
      </Key>
      <Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
      <Property Name="c0" Type="nvarchar(max)" />
      <Property Name="c1" Type="nvarchar(max)" />
    </EntityType>

Reload the project in Visual studio.

And finally add the attribute c1 to the model.

score 2 · Answer 4 · answered Feb 16 '19 at 08:08

Step 1: Double click on .edmx file. (Diagram Window will open)

Step 2: On the diagram window, right click and select Update model from database... (Now it will update only in update storage but not in model)

Step 3: Right click on Model.tt file and click on Run Custom Tool (Now it will be updated in model too)

That's It!

itsmatt · Answer 5 · 2012-03-14T19:14:42.737

1

If I understand your question and your example, once you do the update model from database step and you're sitting there on the Model.edmx diagram, you can highlight the property in the class that you want to change and show the properties on it, and change the Nullable property for it to Nullable: True. That's at least one way to do this.

I believe the idea here is that conceptual model (that isn't being changed from non-null to nullable) can actually differ from the underlying database table and so it doesn't change that part and that difference might be exactly what you intend. The two ways I handle this are either doing the remove & add as you mentioned or more typically I manually set the properties as I mentioned.

edited Mar 14 '12 at 19:14

answered Mar 14 '12 at 19:02

itsmatt

31,265
10
100
164

Yes, I think I follow what you say. In my case I had a non nullable field, made my edmx file. Then I changed my entity to nullable. It then complained that the db is not nullable while the entity is. So I updated the db to be nullable as well and used "Update model from database". But, as you noted it doesn't change the null/not null property. In this case I couldn't just change the entity without generating an error. So I removed and added back the entity and it was okay. Not a big deal, but I wondered if there is a way to do this better. – dtc Mar 14 '12 at 19:25

score -1 · Answer 6 · answered Mar 29 '18 at 13:33

-1

Firstly, double click .edmx file
Secondly, Right Click on the Empty space and select "Update Model From Database"
Thirdly, Select Refresh tab on the menu bar.
Lastly, Select the table you want to refresh and select Finish..

answered Mar 29 '18 at 13:33

adi sinha

15
2

2

Incorrect. There is no choice on what tables to Refresh - all of them are always refreshed using the flow described in the accepted answer. – Niklas Wulff Jul 10 '18 at 11:41