How to Map Stored Procedures using the ADO.NET Entity Framework
This example provides the basic elements of schema syntax required to map a stored procedure to an Entity Data Model implementation. Implementing stored procedures currently requires manual modifications to the *.edmx file. This example describes the schema syntax and demonstrates how to use stored procedures now.
The sample uses the AdventureWorks database that ships with SQL Server 2005. Only a subset of this large database will be required. You can find an implementation of the model in the documentation topic: http://msdn2.microsoft.com/en-us/library/bb387147(VS.90).aspx . The AdventureWorks Sales Model is an Entity Data Model (EDM) implementation based on the tables in the AdventureWorks sample.
Five entities are declared:
- Address
- Contact
- Product
- SalesOrderDetail
- SalesOrderHeader
This example shows how to implement a stored procedure to return the data contained by the SalesOrderDetail tables related to a single SalesOrderHeader. (The FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID association in this model can do the same thing as this example).
Execute the following query command to implement the stored procedure in the Adventureworks database:
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.GetOrderDetails', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.GetOrderDetails;
GO
CREATE PROCEDURE dbo.GetOrderDetails
@SalesOrderHeaderId int
AS
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount,
rowguid, ModifiedDate
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderHeaderId;
GO
Store Schema Definition Language (SSDL) Requirements
When you select the GetOrderDetails stored procedure along with the five tables in the Entity Data Model wizard, a Function element is generated as part of the AdventureWorks Sales Model. The result in the ssdl schema segment of the *.edmx file will look like this.
<Function Name="GetOrderDetails" Aggregate="false"
BuiltIn="false" NiladicFunction="false"
IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion"
Schema="dbo">
<Parameter Name="SalesOrderHeaderId" Type="int" Mode="in" />
</Function>
To see this syntax, open the *.edmx file with the Visual Studio XML editor. If the model was generated before you created the stored procedure, simply add the Function syntax to the ssdl segment of the schema inside the Schema tags but not inside the EntityContainer tags.
That's all that's needed in the SSDL segment of the *.edmx file.
Conceptual Schema Definition Language (CSDL) Requirements
Next we need to implement the function import statement in the CSDL segment of the *.edmx file. Add the following XML to the EntityContainer of the csdl segment:
<FunctionImport Name="GetOrderDetails"
EntitySet="SalesOrderDetail"
ReturnType="Collection(AdventureWorksModel.SalesOrderDetail)">
<Parameter Name="SalesOrderHeaderId" Type="Int32" Mode="in">
</Parameter>
</FunctionImport>
That takes care of the CSDL schema requirements.
Mapping Specification Language (MSL) Requirements
Next, the function import from the CSDL model must be mapped to the SSDL model in the MSL portion of the *.edmx file (look for “C-S mapping content”). The function is mapped in the following syntax inside the EntityContainerMapping:
<FunctionImportMapping FunctionImportName="GetOrderDetails"
FunctionName="AdventureWorksModel.Store.GetOrderDetails"/>
Rebuild the model, and the stored procedure with the required parameter should show up in the Object Browser as a method on the AdventureworksEntities namespace: GetOrderDetails(int).
Using the Method in Code
The stored procedure is used in the following code to enumerate results in a foreach loop.
using (AdventureWorksEntities db = new AdventureWorksEntities())
{
int soHeaderNumber = 43659;
foreach (SalesOrderDetail order in db.GetOrderDetails(soHeaderNumber))
Console.WriteLine("Header#: {0} " +
"Order#: {1} ProductID: {2} Quantity: {3} Price: {4}",
soHeaderNumber, order.SalesOrderDetailID, order.ProductID,
order.OrderQty, order.UnitPrice);
}
The output should look like this:
Header#: 43659 Order#: 1 ProductID: 776 Quantity: 1 Price: 2024.9940
Header#: 43659 Order#: 2 ProductID: 777 Quantity: 3 Price: 2024.9940
Header#: 43659 Order#: 3 ProductID: 778 Quantity: 1 Price: 2024.9940
Header#: 43659 Order#: 4 ProductID: 771 Quantity: 1 Price: 2039.9940
Header#: 43659 Order#: 5 ProductID: 772 Quantity: 1 Price: 2039.9940
Header#: 43659 Order#: 6 ProductID: 773 Quantity: 2 Price: 2039.9940
Header#: 43659 Order#: 7 ProductID: 774 Quantity: 1 Price: 2039.9940
Header#: 43659 Order#: 8 ProductID: 714 Quantity: 3 Price: 28.8404
Header#: 43659 Order#: 9 ProductID: 716 Quantity: 1 Price: 28.8404
Header#: 43659 Order#: 10 ProductID: 709 Quantity: 6 Price: 5.7000
Header#: 43659 Order#: 11 ProductID: 712 Quantity: 2 Price: 5.1865
Header#: 43659 Order#: 12 ProductID: 711 Quantity: 4 Price: 20.1865
Mike Dodaro
Programming Writer, ADO.NET
