I’ve seen three problems with updating a Stored Procedure after adding it to the entity framework:
- The Function Import created to call the Stored Procedure wasn’t updated (incorrect, out-of-date parameters)
- The Complex Type created for the Stored Procedure result set wasn’t updated (incorrect, out-of-date fields)
- Get Column Information in Edit Function Import Popup doesn’t return any column
When a stored procedure is added to a conceptual model, it is called a function import. Adding a function import allows you to call the corresponding stored procedure from your application code. A function import can return collections of simple types, EntityTypes, or ComplexTypes, or no value.
To Create a function Import:
- Do one of the following from the Model Browser:
- Open the Stored Procedures folder (in the storage model information) and double-click a stored procedure that does not have a corresponding function import.
– OR –
- Right-click the Function Imports folder (in the EntityContainer node of the conceptual model information) and then select Add Function Import.
The Add Function Import dialog box appears.
- Fill in the settings for the new function import.
- Specify the stored procedure for which you are creating a function import in the Stored Procedure Name field. This field is a drop-down list that displays all the stored procedures in the storage model. If the desired stored procedure is not available, you may need to update your storage model.
- Specify the name of the function import in the Function Import Name field.
- Specify one of the four basic return types: None, Scalars, Complex, or Entities, and select the specific return type from the available drop-down list. If you choose Complex, the Entity Designer can create a new complex type with properties that correspond to the columns returned by the stored procedure.
- Click Get Column Information to retrieve column information.
- Click Create New Complex Type.
- Edit the name of the complex type in the Complex drop-down list.
When you click OK, a new complex type is added to the conceptual model and the return type of the function import is set to this new type
- Click OK.
Points of Interest
Open the .edmx file (in the GUI designer), right-click in some open space and select “Update Model from Database”.
Click Finish (when able) on the pop-up window, and your Stored Procedure signature should be updated (along with any Function Imports).
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.
Just add SET FMTONLY OFF after BEGIN statement in Stored Procedure and SET FMTONLY ON before END statement in the same Stored Procedure.
CREATE PROCEDURE [my_SP1] @ID INT AS BEGIN SET FMTONLY OFF SELECT col1, col2 FROM table1 SET FMTONLY ON END
And most importantly please don’t add anything manually to .edmx, .context .tt and POCO entities [except manual created entity classes] because updating model from database will eradicate all changes made manually, use POCO classes as much as possible.
About the Author