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

  • Article

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

  1. Open the SSDL file.

  2. 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

  1. Open the CSDL file.

  2. 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

  1. Open the file.

  2. Add the following EntityContainerMapping syntax.

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

  3. Rebuild the model.

See Also

Tasks

How to: Execute a Query Using a Stored Procedure (Entity Framework)

Concepts

Stored Procedure Support (Entity Framework)
ModificationFunctionMapping (EntityTypeMapping)
ModificationFunctionMapping (AssociationSetMapping)