Common Problems with Entity Framework for Importing Store Procedure

Standard
Three common issues occurs while adding store procedure to edmx file of Entity Framework

Introduction

I’ve seen 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 Edit Function Import Popup doesn’t return any column

Background

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:

  1. 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.

  2. 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.
    1. Click Get Column Information to retrieve column information.
    2. Click Create New Complex Type.
    3. 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

  3. Click OK.
    Reference: [http://msdn.microsoft.com/en-us/library/vstudio/bb896231%28v=vs.100%29.aspx]

Points of Interest

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 able) 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 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.

Happy Coding.

History

version 1.0

About the Author

Muhammad Hassan Tariq

Software Developer TRG/IBEX-Global
Pakistan Pakistan
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s