Common Problems With Entity Framework For Importing Stored Procedure

I have seen the following three problems with updating a Stored Procedure after adding it to the Entity Framework:

  1. The Function Import created to call the Stored Procedure wasn't updated (incorrect, out-of-date parameters)
  2. The Complex Type created for the Stored Procedure result set wasn't updated (incorrect, out-of-date fields)
  3. Get Column Information in the Edit Function Import Popup doesn't return a column

First Problem

Open the .edmx file (in the GUI designer), right-click in some open space and select "Update Model from Database".

Click Finish (when you are able to) on the pop-up window and your Stored Procedure signature should be updated (along with any Function Imports).

Second Problem

Open the "Model Browser" window while you have the .edmx open. In the "EntityContainer: ..." node you should see a "Function Imports" node.

Inside this you'll see your Function Import mapped to your Stored Procedure, double-click it to open the same window you used to create it, but now populated with its data.

Now, click "Get Column Information" (look at the grid below the button to see what will be changed), then the "Update" button next to the "Complex" radio button choice. Click OK and the Complex Type for your result set should be updated.

Third Problem

Just add SET FMTONLY OFF after the BEGIN statement in the Stored Procedure and SET FMTONLY ON before the END statement in the same Stored Procedure.

  1. CREATE PROCEDURE [my_SP1]       
  2.    @ID INT   
  3. AS   
  4. BEGIN       
  5.    SET FMTONLY OFF            
  6.    SELECT col1, col2       
  7.    FROM   table1  
  8.    WHERE col1 = @ID  
  9.    SET FMTONLY ON   
  10. END  
And most importantly please don't add anything manually to an .edmx, .context .tt and POCO entities (except manually created entity classes) because updating a model from a database will eradicate all changes made manually, use POCO classes as much as possible.

Happy coding.