Automatic Generation of Stored Procedure Return Types

A new feature of the Entity Framework in .NET 4.0 is the ability to return collections of complex type instances from stored procedures. In the next public release of the designer, we have enriched this functionality by adding the ability to automatically create these complex types by querying stored procedure metadata from the database server. To demonstrate this feature, we will use the Northwind database, and we will focus on a stored procedure called “CustOrdersDetail”. Looking at the designer’s model browser window after we reverse engineer Northwind, we see:

Double-clicking “CustOrdersDetail” brings up the “Add Function Import” dialog:

We click “Get Column Information” in order to see the metadata returned by the server about this stored procedure’s return columns. Then, we click on the “Create New Complex Type” button, and the designer automatically creates a new complex type that matches the shape of the data returned from the stored procedure:

To make life more interesting (and realistic,) let’s rename some of the properties in the generated complex type: ProductName to Name, and UnitPrice to Price. To keep things running, we then need to fix the function import’s mappings to look like this:

Finally, to simulate some real-world churn, we change the stored procedure’s return shape: We’ll rename “UnitPrice” to “UnitPricing”, add a new column called “Foo”, and delete “Quantity”:

ALTER PROCEDURE CustOrdersDetail @OrderID int
AS
SELECT ProductName,
    UnitPricing=ROUND(Od.UnitPrice, 2),
    Discount=CONVERT(int, Discount * 100),
       Foo = '!', 
    ExtendedPrice=ROUND(CONVERT(money, Quantity * (1 - Discount) * Od.UnitPrice), 2)
FROM Products P, [Order Details] Od
WHERE Od.ProductID = P.ProductID and Od.OrderID = @OrderID

That done, we double click on the function import again, get column information, and this time, click on the “Update” button. The designer will now inspect the mappings, the existing complex type, and the new return shape, and produce these proposed changes to the complex type:

As you can see, “Foo” will be added, and “Quantity” will be deleted. In addition, the mappings are used to map “Name” to “ProductName” meaning no action will be taken for it. Finally, since “UnitPricing” is seen as new, “Price” will be deleted and “UnitPricing” will be put in its stead. Since we do not want this, we will rename the column in the function import’s mapping to “UnitPricing” and rerun the above process. This time we get:

Which is what we would want.

In conclusion, the designer now exposes the Entity Framework’s new ability to return complex type instances from stored procedure. On top of this, it adds some features to make this capability easier to use and to iterate over.