Stored Procedures and updating EDMX
C#Entity FrameworkC# Problem Overview
I have had endless issues with stored procedures and EDMX. I created a procedure, updated the model from the database, and all worked. I then removed a column and added a new one in the stored procedure. I updated the model, but the EDMX didn't seem to refresh the proc definition.
I dropped the proc, and did an update, but manually had to delete all references to the proc. I ended up just renaming the proc and importing via model update from database.
I just had the same issue. Added a new column, and renamed an existing column. I refreshed the EDMX via update model from database, but there were no changes to the EDMX, and obviously at run time, it fails. How should a stored procedure update be done with Entity Framework?
I removed the proc, regenerated the model, deleted the 'cs' files for the proc, compiled, then added the procedure to the database again, regenerated the model, and bang! It adds the same model 3 times, with only the last one being right. Why does it keep bringing back the old versions?
C# Solutions
Solution 1 - C#
(This solution is for EF 6. I have not tried in other EF versions. It works nice.)
Go to Model browser. MyStoreProc is the name of the stored procedure (as an example). MyStoreProc will appear in 3 places.
- 1st place- Under Complex Types-> as MyStoreProc_result
- 2nd Place- Under Function Imports -> as MyStoreProc
- 3rd Place - Under Stored Procdures/ Functions -> as MyStoreProc
Delete all three from model. Save the edmx (by clicking in the window then ctrl+S). Then right click and click update the model from database. Then add the updated stored procedure and Save again.
Solved without any hassle :)
Solution 2 - C#
To refresh an existing stored procedure in edmx file,
- Go to "Model Browser" > "Function Imports" > find the desired stored procedure class > right click and click on "Edit"
- In "Edit Function Import" form, in "Returns a Collection Of" section, click on "Update" button
- Click "OK" to finish the refresh.
Solution 3 - C#
Follow these steps:
Step 1: Open Edmx
Step 2: Open Model Browser
Step 3: Open Complex Types and remove your procedure_Result
Step 4: Open Functions Imports and delete your procedure
Step 5 Open StoredProcedur and Functions and delete your procedure
Step 6: Save Edmx (Ctrl+S), Clean Solution , Update Model From Database and select your procedure which you would like to get updated then finally clean, build solution.
Done !
Solution 4 - C#
The only fix I could find was to close the EDMX, and manually edit the XML, which, even after removing the stored proc from the database, and updating the model from the database - still had mention of the proc. Removing the lines from the XML has solved the issue.
Solution 5 - C#
First of all do refresh your SP through "Update Model from Database", if it is working, fine, if not and throwing the same exception then do follow the steps given below. 1.Double Click on .edmx file.
- In Model Browser, Delete SP from Complex Types, Function Imports and Stored Procedures/Functions.
- Save All. 4.Add again your SP through "Update Model from Database". 5.Save All.
Solution 6 - C#
(Tested for EF6 in Visual Studio Community 2019 v16.8.5)
Go to Model browser and browse to your procedure. Either of the following two places:
- Model > Function Imports > myProcedure
- Store > Stored Procedures / Functions > myProcedure
(If you are still not sure how to browse to your procedure, follow illustrations/images in Ishwor Khanal's post.)
Right click on your procedure, select "Update Model from Database..." from context menu.
Click on "Refresh" tab
Open "Stored Procedures and Functions" and then "dbo".
Select the Stored Procedure you want to update and then click "Finish".
Done! (Lesser hassle than deleting and then importing again ;-) )