ADO.Net Entity Designer in VS 2010 - Stored Procedure Return Type Shape Sensing
This blog post will talk about some of the details of a lesser known feature in the the Entity Designer in VS 2010 which enhances user productivity by enabling discovery of the return type of stored procedures from within the Function Import dialog box. Further it allows the Return Type to be defined as a Complex Type which can be created or updated from within the same dialog box when the stored procedure changes without requiring an Update Model from Database operation to be run.
The corresponding Help documentation for the related features are as follows:
- How to: Map a Function Import to a Complex Type
- How to: Import a Stored Procedure
- Walkthrough: Retrieving Entity Types with a Stored Procedure
Let’s look at the feature with an example and some screenshots.
Let’s start by creating a new ADO.Net Entity Data Model file (an EDMX file) which connects to the Northwind database and pulls in a few of the stored procedures from the database as shown below.
Once the wizard completes, you will see that the sproc entries are present in the Store section in the model browser (which corresponds to the SSDL section of the file) as shown below. You can also see that buried under the EntityContainer node there is a “Function Imports” node which is currently empty.
Next, let’s go ahead and create a new Function Import, either via the Add context menu on the canvas (as shown below) or the Function Imports node in the Model Browser.
This will bring up the Add Function Import dialog box in which you can select the Stored Procedure for which you want to create a Function Import. Notice that when you select a sproc the “Get Column Information” button is activated.
Clicking on that button results in a call to get the shape of the stored procedure and this is then displayed in the grid below including information on what is the EDM Type and Db Type for each column, along with additional information such as Nullable, MaxLength etc.
Clicking on the “Create New Complex Type” button does what you would expect by creating a new ComplexType with the shape matching the sproc selected. You can also selected an existing Entity, a Scalar or None instead of the complex type option. Go ahead and save this function import at this point.
Now let’s go ahead and update the selected sproc to return an additional column so that the shape is different from what was originally sensed.
Now when you open the existing Function Import, you can do another “Get Column Information” to sense the new shape off the stored procedure which has been updated.
Which brings in the new column that the sproc is now returning. Viola!
But wait, there is more! At this point, you have the option to either create a new complex type matching this new shape, or conveniently update the existing complex type which makes managing changes to sprocs so much easier on the Entity Framework side. You don’t need to do an Update Model from Database operation and refresh the sprocs and then manually update the corresponding complex types to the new shapes returned etc. It can all be done conveniently from within the Function Import dialog box.
If you hit any issues while working with the Entity Framework Runtime or the Designer, be sure to report it on the ADO.Net Entity Framework Forums on MSDN and someone is sure to help you out. Hope you enjoy working with the Entity Framework in Visual Studio 2010.