Adding a New Column to an Existing Table in Entity Framework

Question

I have added a new column to a table in my database. The table is already defined in the existing Entity Framework model. I've been through most of the items here on how to do this and it still fails.

A little background, this entity model has not been updated in at least 3 years. So aside from the column I'm adding I know there have been a number of other columns that have been added in that time, but never included. I took over the project about 9 months ago and have never been able to successfully update the model.

First attempt:

• Opened the Model in Visual Studio
• Right Clicked on the Background
• Clicked on "Update Model From Database..."
• Clicked on the Refresh Tab
• Selected Tables
• Highlighted the specific table
• Clicked Finish

Result:

• Classes for Dozens of tables that were in my model were deleted
• The table in question was not updated

Second Attempt

• Restored all Source
• Same as above but
• After opening the Update Wizard, Clicked the Delete Tab
• Selected Tables
• Clicked Finish
• All the Tables were deleted
• Saved the EF Model/Closed/Opened it
• Went back to the Update Wizard Add Tab
• Clicked Tables
• None of my tables were displayed when I expanded everything
• Checked the checkbox at the Tables level

Result

• None of my tables were added back, but anything that was not originally included was added

Third Attempt

• Restored all source
• Deleted the two .tt files
• Opened the Update Wizard
• Clicked Add for Everything

Result

• Nothing was recreated, no .tt files or anything else.

Fourth Attempt

• Restored Source
• Deleted Table from the EF Model
• Opened Update Wizard
• Clicked Add Tables

Results

• Classes for Dozens of tables that were in my model were deleted
• The table in question was not added back

Final Attempt

• Added entity manually to model

Result

• Code all compiled and ran, but values were never retrieved from the DB or updated

Any help or direction that could be provided would be greatly appreciated as I'm at a critical point and have to get the model updated.

Poff

That "update model from db" screen is a little deceiving. Even though you're only highlighting certain tables in the Refresh tab, it actually refreshes/updates ALL of them. I'm thinking there's no real checkboxes next to each of the table names you want to "Refresh". So, I imagine it's Refreshing all of them, all the time. – Kienan

Well, that is kind of what I was beginning to think but it still doesn't explain what is going on. All of the tables are in the model, but when I run the database update it just deletes alll the .cs files but leaves them in the model when I display it. I've also added a brand new table to the database and then run the update and I end up with the new table displayed in the model, all of my .cs files deleted and the new table added. I'm clearly doing something wrong. Thanks – Poff

Answer 1

The "Update Model from Database" is hard/slow to use and is prone to errors. It generates other stuff that you probably don't want/need. So manually adding the column that you need will work better. I suggest you do it outside the VS editor since depending on how many models/tables, it can be very slow opening the file in VS.

1. So in Windows Exlorer, right-click on the *.edmx file and open with Notepad (or Notepad++/Textpad).

2. Search for the text <EntityType Name="YourTableNameToAddColumn">.

3. Add the property <Property Name="YourNewColumnName" Type="varchar" MaxLength="64" />

4. Search for the text <MappingFragment StoreEntitySet="YourTableNameToAddColumn">

5. Add mapping to the new column <ScalarProperty Name="YourNewColumnName" ColumnName="YourNewColumnName"/>

6. Save the *.edmx file

Twoseater

what about the .ssdl files? Do you have to edit those too? In our case we have OracleModel.ssdl and SqlModel.ssdl. – Damages

Sounds like this could be the solution I'm looking for but after doing steps 1-6 I get "Error 2009: Content not valid. The conceptual side Member or Property 'xxxxx' specified as part of this MSL does not exist in MetadataWorkspace." on the new Scalar property line under <EntityTypeMapping TypeName="yyyy"> – Empale

@ChrisWalsh Did you ever solve this? Having same problem. – Carr

Sorry @Bojan, I didn't. I tried it, didn't work and went back to using the (hard/slow to use) VS designer. – Empale

And now I am getting this exception: The conceptual side Member or Property '' specified as part of this MSL does not exist in MetadataWorkspace. – Judon

This worked for me. Matched the new column against a known good one and got it working. Must be something screwed up in the EDMX file to not auto generate and update the issues, but this at least lets me get it working for the moment. – Antiphonary

@JamshaidKamran Just run through the .edmx file and add in the <EntityType Name="YourColumnToBeAdded/ColumnAdded"> the property name = "Column Name" Type = "Datatype" – Anse

This answer has a couple of vague claims that I'd like to see materialized (prone to errors, stuff that you probably don't want/need). Also, it's rather weird to replace an allegedly error-prone process by an even more error-prone process. Manually editing an EDMX file is about the last thing you want to do. Often, simply reopening the project in VS will be enough to get rid of "errors" that seem to be related to stale meta data in memory. – Hypodermic

I had to do one extra step of adding the column to the designer to resolve the issue @JamshaidKamran was having – Hydantoin

Answer 2

1. Right click on the *.edmx file and open with Visual Studio's XML editor (or Notepad/Notepad++/Textpad).

2. Search for the text <EntityType Name="YourTableNameToAddColumn">.

3. Add the property e.g.: <Property Name="YourNewColumnName" Type="varchar" MaxLength="64" /> (these ones are SQL types, see existing columns for an example of how they should look).

4. Search for the text again <EntityType Name="YourTableNameToAddColumn">, there is a second one.

5. Add the property e.g.: <Property Name="YourNewColumnName" Type="String" MaxLength="64" FixedLength="false" Unicode="true" /> (these ones are EF types, see existing columns for an example of how they should look).

6. Search for the text <MappingFragment StoreEntitySet="YourTableNameToAddColumn">.

7. Add mapping to the new column <ScalarProperty Name="YourNewColumnName" ColumnName="YourNewColumnName"/> (Note: these are in reverse order, newest first)

8. Save the *.edmx file

9. After that update the edmx model of your table in the (auto-generated) entity framework .cs files public string YourNewColumnName { get; set; }

Construct

Upvoting because this solved my problem after many, many hours of wonderful research and attempts that all failed. I too have an older EDMX project in my solution that can not be updated from the database anymore. All new work is migrating data layer stuff into a proper api service; but smaller MOB type stuff sometimes requires maintaining status quo. Many thanks! – Croce

Also, FYI, you will need to add a DbSet entry into the *Model.Context.cs file AND edit any class files in your model. In my case, whereas the table is called EmployeeTracking, there would be a .cs file called EmployeeTracking.cs which contains the actual entity definition. – Croce

Just want to note that even though you can edit the .edmx "diagram" in your project so that it appears to accomplish what this answer proposes, but in a GUI setting, this does not work. Manually editing the .edmx file in a text editor solved the issue for me. – Engineering

You saved my hours thanks, I didn't notice there is second Entity type exists there! – Censorious

I am getting an exception for invalid column name when I tried the above mentioned solution, but in my case I have 10 new columns. Or is this solution only for 1 new column? – Asynchronism

Don't forget to right click on the EDMX file on Visual Studio and click on "Run Custom Tool" to regenerate the design file – Wideangle

Best Solution out there for sure – Intumescence

Saved hours of time. Also, I had to intstall EF Power Tools, right clicked on my edmx => Entity Framework => Generate Views. A 'Model1.View.cs' – Limbert

that's the answer I looked for – Kweiyang

Answer 3

An addendum to the answer by alltej above, and answering Chris Walsh's reply that he is getting 'The conceptual side Member or Property 'xxxxx' specified as part of this MSL does not exist in MetadataWorkspace." on the new Scalar property line under '

You have to make sure that you search 'Add the property ' in TWO places within your .edmx file , otherwise you will get Chris's error

Ite

Answer 4

Figured out the problem. When I generated the model I was getting an Error 113:

Multiplicity is not valid in Role.

I didn't notice it among the other 16307 errors that were generated when the creation failed. Once I fixed that everything worked fine.

Thanks

Poff

you should accept this as the answer even if it's your own, it will help your profile down the road here stackoverflow.com/users/4695405/… – Irfan

Answer 5

I just found this question when I had a situation where I added some columns to a table, and my edmx file would update just fine; however, the code generation was not triggering properly, so my .cs files were not updating. I right clicked on the Entities.tt file and selected "Run Custom Tool" which runs the text transformations, which fixed it for me.

Landlocked

Answer 6

I ran into this problem a while ago and was able to deal with it by removing the particular table from the model, then doing the "Update model from database" step, selecting that table.

Mona

Answer 7

Make sure your 'Model.edmx' file has the name same as 'Model.tt' if not, just rename it the same name with the *.tt file. Then delete entities from designer and import again by 'update model from database'.

Woodsman