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.

CREATE PROCEDURE [my_SP1]
   @ID INT
AS
BEGIN
   SET FMTONLY OFF
   SELECT col1, col2
   FROM   table1
   WHERE col1 = @ID
   SET FMTONLY ON
END

And most importantly please don't add anything manually to a .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.