53

I am using .NET 3.5 SP1, VS 2008 with the entity framework. I originally added a stored procedure to my model which took 2 parameters. Later, I changed the sproc to just take 1 parameter. I ran the Update Model From Database menu option and confirmed that my sproc was listed in the Refresh tab. Finished up the wizard, cleaned and rebuilt the solution and the model never changed the signature of the sproc.

Why isnt it updating? Is the Update model from database feature broken? Am I missing something? Thanks.

forsvarir
  • 10,749
  • 6
  • 46
  • 77
Jeff
  • 13,943
  • 11
  • 55
  • 103

8 Answers8

134

I've seen two problems with updating a Stored Procedure after adding it to the entity framework:

  • The Function Import created to call the SPROC wasn't updated (incorrect, out-of-date parameters)
  • The Complex Type created for the SPROC's result set wasn't updated (incorrect, out-of-date fields)

Updating EDMX model (function import)

  1. Open the .edmx file (in the GUI designer).
  2. Right-click in some open space and select "Update Model from Database".
  3. Click Finish (when able) on the pop-up window. Your SPROC's signature should be updated (along with any Function Imports).

Updating stored procedure complex type

  1. open the "Model Browser" window while you have the .edmx open.

  2. Expand the ContentModel, EntityContainer: ..., and then Function Imports nodes. Look for the function import for your stored procedure and double click it. The same window you used to create it will open, but now populated with its data.

  3. Click the Get Column Information button (look at the grid below the button to see what will be changed).

  4. Click the Update button next to the "Complex" radio button choice.

  5. Click OK, and the Complex Type for your result set should be updated.

DaveD
  • 2,196
  • 1
  • 23
  • 33
  • 21
    Is it me or is this an overly complex and obfuscated approach to what seems to be designed to expedite app creation? Also, this issue is still happening with EF6 =( Also, thanks for this post, for the longest time I've been cursing EF framework as I deleted and re-created my edmx every time I made an update. – Josh Mar 06 '14 at 06:31
  • 9
    No Josh, it's not just you. It IS overly complex and can waste hours trying to get Entity Framework to work with stored procedures. And why should EF need to work with a stored procedure? Because sometimes that's the best solution. Don't get me wrong EF is good but surely the stored procedures should be as easy to find as the tables, not hidden away like an elderly relative one is ashamed of. – Daniel Hollinrake Feb 03 '15 at 10:25
  • I am using EF6 and I am having problem with the same issue, but this answer didn't worked for me, so I'll delete and create my data model again. – MelgoV Feb 26 '15 at 19:41
  • 2
    Thank you!! In my case I was right-clicking (and choosing update) on the stored proc from the Function Imports list - not DOUBLE-CLICKING!!! Just noticed that you can right-click and then choose EDIT!!! – Dave Sep 14 '15 at 10:50
  • Wov, I finally resolved this issue after several month of usage of EntityFramework. I just though if the EF can save my time, because of all related problems and complexity one have to study. – Tomas Kubes Dec 22 '15 at 15:39
13

You don't have to edit the edmx / xml. DO THIS:

When you bring a stored procedure into the EDMX (I'm talking about one that returns a result set, but it's going to be a similar process), 3, not 1, items are created, and that is the source of the problem. (*** the second source of the problem is that you have to explicitly SAVE after deleting objects, before re-importing objects, otherwise the newly re-imported objects will clash with what's not been truly deleted (by saving) from the edmx.

SO! Do this:

In Model Browser, usually on the left, paste the name of your stored procedure into the search box, click enter (or search). It will land on the first instance - probably the stored procedure itself. Right click and delete. Now, put cursor back in search box -> hit enter or search again. Now you will land on the function import. Right click and delete. Do the same thing a third time - this will land on the complex type representing the result set. Delete it. Now you're done NOT!!!!!. You have to save what you just did, so that the deletions are written (un-written) in the edmx.

Now, go get the stored procedure again (updated model / select the stored proc). NOW CLICK SAVE AGAIN.

That will work every time. The key is make sure when using the model browser to search ALL instances of the stored procedure name appearing in the model browser, and because of that, it's better not to fuss around with the default naming conventions. Second key is to SAVE after you complete any step.

jamescnectnet
  • 131
  • 1
  • 2
6

If you need to change data-types of the columns in the resultset --> in the beginning of the stor proc add set fmtonly off

Then use Model Browser --> Function Import --> Edit Your Proc --> Get Column Information button --> Update button

This is how Neil does it :)

alex
  • 61
  • 1
  • 1
1

It took a lot of manual editing on the XML and couple restarts of Visual Studio but I was finally able to get VS to forget the previous signature. Then I just used the normal EF wizard to add it back in and it worked just fine.

Jeff
  • 13,943
  • 11
  • 55
  • 103
1

if you change the signature of the stored proc you will need to update any complex type created in the model via function import.

kram
  • 31
  • 3
0

Had a similar problem in EF4 where there is no option to refresh individual sps, it's meant to do it when you click refresh in the update wizard but it doesn't work reliably. I had to in the model browser delete the complex types for the return values, and also the function import. Then go into the edmx file with the XML editor and remove the reference to the stored proc (couldn't find a way of doing this in the model browser). Then re-add the sps.

Rob Sedgwick
  • 4,342
  • 6
  • 50
  • 87
0

You can also erase all evidence of the SP in the following places, theses files can be found in the Model Browser when you open the .edmx file:

  • YourModel > Complex Types;
  • YourModel > Function Imports;
  • YourModel.Store > Stored Procedures / Functions.

After delete it you can add it back with no problems.

Ariel
  • 911
  • 1
  • 15
  • 40
0

You have to delete and add stored procedures back as entity framework needs to generate a new definition for your procedure. I guess you need to redo the function import.

Basically if you had change the logic of the procedure and not the return type or signature you dont need to add it again. It will work fine but if you change the return type or signature ef needs to generate and map it to a new definition that is the way it works.

Vishal
  • 12,133
  • 17
  • 82
  • 128
  • I tried this and it didnt work. Since the "Update Model..." function never actually synced the model with the database, performing a function import on the outdated sproc just recreated the old signature. – Jeff Feb 14 '11 at 18:02
  • So you basically could never delete the procedure from the model and add back the model with the new updated procedure ? – Vishal Feb 14 '11 at 18:20
  • correct. thats how it seems. now im am trying to forgo the vs designer and edit the XML directly. we'll see if that works better – Jeff Feb 14 '11 at 18:26
  • @Jeff..Well if you are editing the designer directly..as far as i know it will regenerate the designer code everytime you make any changes to the model..I think you should just try and do it again maybe close reopen solution....and try different things..basically your model and designer should be in sync..otherwise you will have issues..unless you move towards POCO or something similiar.. – Vishal Feb 14 '11 at 18:41