ADO.Net Entity Designer in VS 2010 - Stored Procedure Return Type Shape Sensing

  • Article

The Entity Framework in .Net Framework 4.0 contains a bunch of new functionality mentioned here and here.

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:

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.

clip_image001

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.

clip_image002

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.

clip_image003

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.

clip_image004

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.

clip_image005

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.

clip_image006

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.

clip_image007

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.

clip_image008

Which brings in the new column that the sproc is now returning. Viola!

clip_image009

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.

clip_image010

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.