Working with Stored Procedures in Entity Framework - Part 2

In Part 1 of this two part series, you learned to map stored procedures for the INSERT, UPDATE and DELETE operations in the entity framework designer. EF also allows you call arbitrary stored procedures from your code. These stored procedures might be doing some custom processing and there are at least four distinct types of values they can return back to the calling code:

• Scalar values such as integer or string
• Zero or more records that map exactly with some entity class
• Zero or more records that return data not mapping directly to any existing entity class
• Multiple result sets

Whatever be the case a basic thing you need to do is to import the required stored procedures in the EF model under consideration. In the examples illustrated in this article you will need to import the following stored procedures residing in the Northwind database. Some of these stored procedures are installed as a part of the Northwind database. Additionally you need to create a few on your own. The actual stored procedures and they return values are discussed in more details in the later sections. The following figure shows the "Update Model from Database" dialog of the EF designer:

Once you import the stored procedures in the model, they will be listed under the Function Imports folder inside the model browser (see below).

Next, double click on any of the stored procedure. Doing so will open the following dialog:

This dialog is important because this is where you can change the return type of a stored procedure. The possible return types are:

• None
• Scalars
• Complex
• Entities

The return type of none means stored procedure doesn't return anything. The return type of scalars indicates that the stored procedure under consideration returns a scalar value and its data type can be selected from the dropdown. Most of  the times EF designer automatically detects the data type for you. The return type of complex indicates that the stored procedure is returning records that do not map with any existing entity structure. These records are, therefore, mapped with custom complex types (classes) in .NET. The complex type is generated automatically by EF (more on that later) but if you wish you can also create and map a complex type on your own. The mapping can be changed through the dropdown in front of Complex radio button. The return types of Entities indicates that the stored procedure is returning records that are directly mapping with some existing entity. The name of the entity can be changed from the associated dropdown.

In many cases you won't need to even open this dialog because EF automatically sets the return type for each stored procedure you import. However, in some cases (as you will see it later) you may need to use it to modify the return type mapping. Once the return types of a stored procedure is set you are ready to use it in your code.

Now, that you have the basic idea about calling arbitrary stored procedures let's see one example of each return type (except the return type of None).

Calling a stored procedure that returns a scalar value

In this section you will call a stored procedure that returns an integer (i.e. scalar) value. The stored procedure that you will use is shown below:

CREATE PROCEDURE CustOrderCount
	@CustomerID nchar(5)
AS
BEGIN
	SELECT COUNT(*) FROM ORDERS 
        WHERE CUSTOMERID=@CustomerID;
END

The CustOrderCount stored procedure accepts a CustomerID and returns the number of orders belonging to that customer.

To call this procedure from C# code, first of all import it in your model as outlined at the beginning of this article. Then you can write the following C# code to call it:

 NorthwindEntities db = new NorthwindEntities();
var count = db.CustOrderCount("ALFKI");
int ordercount = count.SingleOrDefault().Value;

The above code creates a data context and then invokes the CustOrderCount() method on it. Notice that importing a stored procedure adds a method to the context class with the same name and parameters as the stored procedure. You can then call that method in your code.

The CustOrderCount() method returns an object of type ObjectResult. See the following signature of the CustOrderCount() method as shown in VS:

The ObjectResult class represents an enumerable results returned by the stored procedure. So, to get the actual value as returned by the stored procedure you need to use SingleOrDefault() method and then use the Value property. If you run this code you should get 6 as the ordercount for CustomerID of ALFKI.

Calling a stored procedure that returns records that do not map with any entity

In this section you will call a stored procedure that returns order history of a customer. The stored procedure selects ProductName and its total Quantity using the following query:

CREATE PROCEDURE CustOrderHist 
  @CustomerID nchar(5)
AS
  SELECT ProductName, Total=SUM(Quantity)
  FROM Products P, [Order Details] OD, Orders O, Customers C
  WHERE C.CustomerID = @CustomerID
  AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID 
  AND OD.ProductID = P.ProductID
  GROUP BY ProductName

The CustOrderHist stored procedure is available with the default installation of Northwind database. Before you call this this stored procedure import it in the model as before. This time the EF designer will also create a CustOrderHist_Result complex type for you. You can confirm this by double clicking on the CustOrderHist stored procedure in the model browser and then by observing its return type. The return value of CustOrderHist stored procedure is collected in this complex type. The following figure shows how this complex type looks like in the model browser:

Ok. You are now ready to call the stored procedure. Write the following C# code:

NorthwindEntities db = new NorthwindEntities();
var history = db.CustOrderHist("ALFKI");
foreach(CustOrderHist_Result item in history)
{
  string product = item.ProductName;
}

As you can see the CustOrderHist() has been called as before. This time, however, its return type is an ObjectResult of CustOrderHist_Result. You can iterate through it to grab the individual instance of CustOrderHist_Result returned. The foreach loop in the above example doesn't perform any meaningful operation on the return value apart from storing the ProductName in a string variable. You can easily replace your own login inside the loop.

In this example you used the complex type automatically created by the EF designer during the import operation. You could have created a complex type manually and then specified it in the return type dialog. In any case, if you wish to receive the return value of CustOrderHist stored procedure you must have a custom complex type. This is obvious because the returned data can't be mapped directly with any of the existing entities (such as Customer and Order).

Calling a stored procedure that returns records that map with an entity

Sometimes the stored procedure you wish to call might return data that can be mapped directly to an existing entity in the model. For example, a stored procedure may return certain records from Customers table. In this case the returned data can be mapped with the Customer entity. Consider the following stored procedure that does something similar:

CREATE PROCEDURE [dbo].[CustOrders] 
  @CustomerID nchar(5)
AS
  SELECT *
  FROM Orders
  WHERE CustomerID = @CustomerID
  ORDER BY OrderID

The CustOrders stored procedure accepts a CustomerID and returns all the Orders records from the Orders table for that CustomerID.

Import the stored procedure as before and then double click on it to open the Edit Function Import dialog. By default EF designer creates CustOrders_Result complex type. Since you know that the stored procedure return value can be mapped with Order entity you can change the return type like this:

As you can see the return type of the stored procedure has been changed to Entities and Order entity is selected in the dropdown.

To call this stored procedure you can use the following C# code:

NorthwindEntities db = new NorthwindEntities();
var result = db.CustOrders("ALFKI");
foreach(Order o in result)
{
  int i = o.OrderID;
  DateTime dt = o.OrderDate.Value;
}

In this case the return type of CustOrders() is an ObjectResult of Order. You can then iterate through the result and access individual Order instance.

Calling a stored procedure that returns multiple result sets

So far so good. However, things can be more complex that this. What if your stored procedure is returning multiple result sets? Consider the following stored procedure:

CREATE PROCEDURE [dbo].[CustAndOrders]
  @CustomerID char(5)
AS
  SELECT * from Customers where CustomerID=@CustomerID;
  SELECT * from Orders where CustomerID = @CustomerID;

The CustAndOrders stored procedure returns two result sets - the first result set from Customer table and the second result set from the Orders table. In such cases EF designer won't be able to map the return value of the stored procedure as expected. If you see the return type of CustOrders in the Edit Function Import dialog you will find it to be CustAndOrders_Result and the CustAndOrders_Result complex type contains properties for Customer data only.

To handle multiple result sets you need to manually modify the .edmx file as outlined below.

Right click on the .edmx file in the Solution Explorer and using "Open With" menu option open it in the XML editor. Locate the function import element for CustAndOrders stored procedure. It will be like this:

 <FunctionImport Name="CustAndOrders" 
  ReturnType="Collection(NorthwindModel.CustAndOrders_Result)">
  <Parameter Name="CustomerID" Mode="In" Type="String" />
</FunctionImport>

Change the above function import definition as shown below (be extra careful!) :

 <FunctionImport Name="CustAndOrders">
  <ReturnType EntitySet="Customers" Type="Collection(NorthwindModel.Customer)" />
  <ReturnType EntitySet="Orders" Type="Collection(NorthwindModel.Order)" />
  <Parameter Name="CustomerID" Mode="In" Type="String" />
</FunctionImport>

As you can see the <FunctionImport> element now has two <ReturnType> sub-elements. They specify the return types to be a collection of Customer and Order entities respectively.

Then locate function import mapping for CustAndOrders stored procedure. In its current form it will be like:

<FunctionImportMapping FunctionImportName="CustAndOrders" 
  FunctionName="NorthwindModel.Store.CustOrders">
  <ResultMapping>
    <ComplexTypeMapping TypeName="NorthwindModel.CustAndOrders_Result">
      ......
    </ComplexTypeMapping>
  </ResultMapping>
</FunctionImportMapping>

As you can see, currently the <ResultMapping> section uses <ComplexTypeMapping> to map the return value with CustAndOrders_Result. Modify the <ResultMapping> as shown below:

...
<ResultMapping>
  <EntityTypeMapping TypeName="NorthwindModel.Customer">
  </EntityTypeMapping>
</ResultMapping>
<ResultMapping>
  <EntityTypeMapping TypeName="NorthwindModel.Order">
  </EntityTypeMapping>
</ResultMapping>
...

As you can see, now you have two <ResultMapping> sections and each uses <EntityTypeMapping> to map the return values to Customer and Order entities respectively.

This completes the .edmx modifications. Make sure to save the .edmx file. The final step is to delete the CustAndOrders_Result auto-generated complex type from the model.

Now, write the following C# code to call this stored procedure:

 NorthwindEntities db = new NorthwindEntities();
var result1 = db.CustAndOrders("ALFKI");
string s1 = result1.FirstOrDefault().CompanyName;
var result2=result1.GetNextResult<Order>();
int s2 = result2.FirstOrDefault().OrderID;

When you cann CustAndOrders() method, initially the first result set is returned. You can access this result using a foreach loop or using any of the supported methods. To get hold of the second result set, you call GetNextResult() method on the first result set. You can then access the second result set as before.