How to: Define a Model with a Stored Procedure (Entity Framework)

Many application developers and database administrators use stored procedures to enforce security, provide predictability, and encapsulate logic on data inside the database. The example in this topic provides the basic elements of schema syntax required to map a stored procedure to an Entity Data Model (EDM) implementation. The stored procedure can then be called by code in an application that uses the data model.

Two kinds of stored procedure mapping are supported by the EDM. For more information about mapping stored procedures that update data, see Stored Procedure Support (Entity Framework).

The examples in this topic are based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

The AdventureWorks Sales Model defines five entities:

Address
Contact
Product
SalesOrderDetail
SalesOrderHeader

The following database scripts and schema excerpts are used to implement a stored procedure that returns the data contained by the SalesOrderDetail tables related to a single SalesOrderHeader. (The FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID association in the Sales Model can do the same thing as this example.).

To create the stored procedure in the database

Use SQL Server Management Studio or query command syntax to execute the following query command, which implements the stored procedure in the AdventureWorks database.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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, LineTotal
   FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @SalesOrderHeaderId;

To implement store schema definition language (SSDL) requirements

Open the SSDL file.
Add the following function syntax inside the schema tags but not inside the EntityContainer tags.

<Function Name="GetOrderDetails" Aggregate="false" 
    BuiltIn="false" NiladicFunction="false" 
    IsComposable="false" 
    ParameterTypeSemantics="AllowImplicitConversion" 
    Schema="dbo">
        <Parameter Name="SalesOrderHeaderId" Type="int" Mode="In" />
</Function>

To implement the conceptual schema definition language (CSDL) requirements

Open the CSDL file.

Add the following FunctionImport 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>

Implement the mapping specification language (MSL) requirements

Open the file.

Add the following EntityContainerMapping syntax.

<FunctionImportMapping FunctionImportName="GetOrderDetails"
  FunctionName="AdventureWorksModel.Store.GetOrderDetails"/>

Rebuild the model.

To create the stored procedure in the database

To implement store schema definition language (SSDL) requirements

To implement the conceptual schema definition language (CSDL) requirements

Implement the mapping specification language (MSL) requirements

See Also

Tasks

Concepts