Stored Procedures and updating EDMX

Question

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?

enter image description here

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?

score 233 · Accepted Answer · answered Oct 01 '15 at 04:21

233

(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 :)

answered Oct 01 '15 at 04:21

SArifin

2,746
1
15
15

This was very helpful, thanks! I have to admit that I was a bit nonplussed because I was looking for this in the edmx diagram and Stored Procedures are not displayed there - only Tables and Views. – Rich Ward Aug 11 '16 at 18:36
1

Good and exact solution this works well in EF 5 as well. – 3 rules Dec 23 '16 at 06:27
9

This certainly works, but what's the idea behind not actually UPDATING the edmx when a refresh is done? It doesn't make any sense. – LarryBud Jan 23 '17 at 20:51
Excellent, worked a charm! I also clear the EDMX of objects and rebuild that by importing everything again. – Mike Upjohn Feb 21 '17 at 09:41
I would suggest not saving the edmx after deleting the old stored procedure (source control can mess up on marking a file for deletion and the re-adding it - and it works fine without the intermediate save) - but apart from that, EXCELLENT answer! – Graham Laight Jun 05 '17 at 16:31
In short EF sucks! I had explicitly checkout files. – Jag Nov 14 '17 at 02:28
Thanks..I knew there's a way for this.I use to just delete the whole edmx and readd everything again. I deleted all three instance and rebuild the ef model. – CyberNinja Oct 01 '18 at 14:47
1

Great help, and time saving. in EF 6.1.3 all work like sharp. thanks – PatsonLeaner Oct 15 '18 at 09:13
It will be interesting for me what guys from Microsoft will say about this. IMHO they should have tab Update where similar to Add/Delete tabs to have tree with checkboxes for stored procedures/functions where you can select which dbo to be updated. Because for now it is not very useful and I should do it by hand every time I change my stored procedure/function. – Miroslav Hristov Aug 18 '19 at 07:40
1

My Model Browswer is blank, and like others have said, simply "Updating" the edmx file does nothing. If I try to delete the code from the Context.cs so that I can re-add the SP, EF justs adds it back to the file when I compile keeping me from being able to re-add the SP. – Mwspencer Dec 12 '19 at 18:17
Very helpful answer! Thank you! We're using EF 6.1.3. – LReeder14 Feb 16 '23 at 17:16

Fábio Nascimento · Answer 2 · 2018-05-22T15:00:23.827

72

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.

edited May 22 '18 at 15:00

answered May 22 '18 at 14:35

Fábio Nascimento

2,644
1
21
27

4

Yep, I am doint it in the same way(Vote Up from me). As I have commented earlier - guys from Microsoft should expose functionality to do this by selecting which stored procedures/functions to update(similar to Add/Delete dbos from database. There is tab "Refresh" which is not very helpful IMO) – Miroslav Hristov Aug 18 '19 at 07:43
1

you are the man, man – Leandro Bardelli Oct 14 '21 at 20:29
1

This should be the accepted answer. There's no reason to go through the process of deleting and re-adding the stored procedure just to pick up a change in the returned columns. – Lex Feb 11 '22 at 17:52

score 17 · Answer 3 · edited Jul 12 '20 at 22:35

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 !

score 7 · Answer 4 · answered Aug 06 '13 at 20:04

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.

score 0 · Answer 5 · answered Nov 14 '17 at 09:41

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. 2. In Model Browser, Delete SP from Complex Types, Function Imports and Stored Procedures/Functions. 3. Save All. 4.Add again your SP through "Update Model from Database". 5.Save All.

score 0 · Answer 6 · answered Feb 22 '21 at 22:28

(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 ;-) )

score 0 · Answer 7 · answered Jul 15 '22 at 22:27

I'd been experiencing the same issue with a SP I was updating. I tried all the solutions suggested here and nothing worked (in fact, the SP had no result columns after the update)

Then I remembered when an SP uses temporal tables it has a weird behaviour.

That's why I added on SQL this line:

SET FMTONLY OFF;

With it, I could update the SP in Visual Studio (used Fábio's solution)

Hope this help anyone who experiences this same problem

Stored Procedures and updating EDMX

7 Answers7

Linked