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.

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

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; }

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

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

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.

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.

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'.