Condividi tramite

Stored Procedure Support (Entity Framework)

  • Articolo

The Entity Data Model (EDM) supports use of stored procedures for data retrieval and modification. Stored procedures can be used to retrieve, insert, update, and delete data.

Many database applications rely on stored procedures to provide the following benefits:

  • Security. Database users can be denied direct access to tables or other database objects. Database administrators need only grant execute permissions on stored procedures to create single entry points for data access. This significantly reduces the attack surface for SQL injection attacks. Input and output parameters configured to use default values enable strict parameter validation. Validation code in stored procedures can limit the available actions.

  • Encapsulation. Complex data logic, explicit transactions and other database operations can be written once in stored procedure code and executed from multiple client applications. Errors, exceptions, and concurrency violations can be handled from server-side code, reducing the number of round trips from client applications. Stored procedure code can be modified without affecting client applications as long as the signature of the stored procedure remains unchanged.

  • Predictability. Database administrators frequently must make sure that client applications do not issue inefficient queries that can adversely affect server performance. Queries in stored procedures can be individually tuned to produce acceptable execution plans. In addition to tuning, well-written stored procedures simplify troubleshooting server-side issues such as blocking and deadlocks.

  • Performance. In some situations, there can be performance gains from using stored procedures. Modern database engines generally treat dynamic SQL statements as efficiently as statements in stored procedures; database administrators maintain more control over performance by enforcing the use of stored procedures.

Stored procedures that return data are called from named functions in the EDM object model. Stored procedures that update data are mapped to entities and associations and are called implicitly when the operations they define would use system-generated methods if the stored procedures were not implemented and mapped.

This topic describes the stored procedures used to update data through mappings specified in the ModificationFunctionMapping schema element. Stored procedures that retrieve data use the FunctionImportMapping element. For a quick-start about stored procedures that retrieve data but do not change it, see How to: Define a Model with a Stored Procedure (Entity Framework).

Another scenario that can be managed by stored procedures is insertion or deletion of an instance of an association between existing entities. Mapping stored procedures to these operations is described in Mapping Association Sets to Stored Procedures (Entity Framework).

By default, the Entity Framework performs operations directly against database tables based on mappings between the conceptual and storage schemas. Configuring the Entity Framework to use stored procedures to update data requires modifications to the storage and mapping schemas.

The following table contains links to topics that explain the use of stored procedures in the several scenarios supported by the EDM.

Operation SSDL requirements MSL requirements CSDL requirements

Execute a query that returns entity objects. For more information, see How to: Define a Model with a Stored Procedure (Entity Framework).

Add a Function element inside the Schema element of the storage model file. This element references a stored procedure that returns an entity type.

Add a FunctionImportMapping element in the EntityContainerMapping element of the conceptual model file.

Add a FunctionImport element in the EntityContainer element of the conceptual model file.

Stored procedures are used to insert, update, and delete entity data. For more information, see How to: Define a Model with Modification Stored Procedures (Entity Framework).

Add Function elements inside the Schema element of the storage model file. Do this once for each insert, update, and delete stored procedure.

Add a ModificationFunctionMapping element in the EntityTypeMapping element for the entity type. This element must define InsertFunction, UpdateFunction, and DeleteFunction elements for the insert, update, and delete stored procedures. When the entity has relationships, an AssociationEnd element specifies the association.

None.

Stored procedures are used to create or delete many-to-many relationships between entity types, which are implemented in the data source using a link table. For more information, see Mapping Association Sets to Stored Procedures (Entity Framework).

Add Function elements inside the Schema element of the storage model file. Do this once for each stored procedure that creates or deletes relationships in the data source.

Add a ModificationFunctionMapping element in the AssociationSetMapping Element element for the association. This element must define InsertFunction and DeleteFunction elements for the stored procedures that create and delete relationships for this association.

None.

Data Modifications and Stored Procedures

Stored procedures used for data modification replace the methods generated by the Entity Framework. Stored procedures are called implicitly, so no changes are required to the EDM defined in the conceptual schema or existing application code.

CSDL

The following conceptual schema definition language (CSDL) segment defines a SalesOrderHeader entity and a SalesOrderDetail entity. Both types are based on the AdventureWorks sample database that ships with SQL Server 2005 and SQL Server 2008. No CSDL modifications to the CSDL schema are needed to use stored procedures to modify EDM data, but the CSDL schema is shown here for completeness. This schema omits some property definitions for brevity. The complete schema can be found in the sample in the AdventureWorks Sales Model (EDM).

      <Schema Namespace="AdventureWorksModel" 
              Alias="Self" xmlns="https://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="AdventureWorksEntities">
          <EntitySet Name="AddressType" EntityType="AdventureWorksModel.AddressType" />
          <EntitySet Name="Contact" EntityType="AdventureWorksModel.Contact" />
          <EntitySet Name="Product" EntityType="AdventureWorksModel.Product" />
          <EntitySet Name="SalesOrderDetail" 
                     EntityType="AdventureWorksModel.SalesOrderDetail" />
          <EntitySet Name="SalesOrderHeader" 
                     EntityType="AdventureWorksModel.SalesOrderHeader" />
          <AssociationSet Name="FK_SalesOrderHeader_Contact_ContactID"
Association="AdventureWorksModel.FK_SalesOrderHeader_Contact_ContactID">
            <End Role="Contact" EntitySet="Contact" />
            <End Role="SalesOrderHeader" EntitySet="SalesOrderHeader" />
          </AssociationSet>
          <AssociationSet Name="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID" 
Association="AdventureWorksModel.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID">
            <End Role="SalesOrderHeader" EntitySet="SalesOrderHeader" />
            <End Role="SalesOrderDetail" EntitySet="SalesOrderDetail" />
          </AssociationSet>

        </EntityContainer>

        
        <EntityType Name="SalesOrderDetail">
          <Key>
            <PropertyRef Name="SalesOrderID" />
            <PropertyRef Name="SalesOrderDetailID" />
          </Key>
          <Property Name="SalesOrderID" Type="Int32" Nullable="false" />
          <Property Name="SalesOrderDetailID"
                             Type="Int32" Nullable="false" />
          <Property Name="CarrierTrackingNumber" Type="String" />
          <Property Name="OrderQty" Type="Int16" Nullable="false" />
          <Property Name="ProductID" Type="Int32" Nullable="false" />
          <Property Name="SpecialOfferID" Type="Int32" Nullable="false" />
          <Property Name="UnitPrice" Type="Decimal" Nullable="false" />
          <Property Name="UnitPriceDiscount"
                             Type="Decimal" Nullable="false" />
          <Property Name="LineTotal" Type="Decimal" Nullable="false" />
          <Property Name="rowguid" Type="Guid" Nullable="false" />
          <Property Name="ModifiedDate"
                             Type="DateTime" Nullable="false" />
          <NavigationProperty Name="SalesOrderHeader"
Relationship="AdventureWorksModel.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
             FromRole="SalesOrderDetail" ToRole="SalesOrderHeader" />
        </EntityType>

        <EntityType Name="SalesOrderHeader">
          <Key>
            <PropertyRef Name="SalesOrderID" />
          </Key>
          <Property Name="SalesOrderID" Type="Int32" Nullable="false" />
          <Property Name="RevisionNumber" Type="Byte" Nullable="false" />
          <Property Name="OrderDate" Type="DateTime" Nullable="false" />
          <Property Name="DueDate" Type="DateTime" Nullable="false" />
          <Property Name="ShipDate" Type="DateTime" />
          <Property Name="Status" Type="Byte" Nullable="false" />
          <Property Name="OnlineOrderFlag"
                        Type="Boolean" Nullable="false" />
          <Property Name="SalesOrderNumber"
                        Type="String" Nullable="false" />
          <Property Name="PurchaseOrderNumber" Type="String" />
          <Property Name="AccountNumber" Type="String" />
          <Property Name="CustomerID" Type="Int32" Nullable="false" />
          <Property Name="SalesPersonID" Type="Int32" />
          <Property Name="TerritoryID" Type="Int32" />
          <Property Name="BillToAddressID"
                        Type="Int32" Nullable="false" />
          <Property Name="ShipToAddressID"
                        Type="Int32" Nullable="false" />
          <Property Name="ShipMethodID" Type="Int32" Nullable="false" />
          <Property Name="CreditCardID" Type="Int32" />
          <Property Name="CreditCardApprovalCode" Type="String" />
          <Property Name="CurrencyRateID" Type="Int32" />
          <Property Name="SubTotal" Type="Decimal" Nullable="false" />
          <Property Name="TaxAmt" Type="Decimal" Nullable="false" />
          <Property Name="Freight" Type="Decimal" Nullable="false" />
          <Property Name="TotalDue" Type="Decimal" Nullable="false" />
          <Property Name="Comment" Type="String" />
          <Property Name="rowguid" Type="Guid" Nullable="false" />
          <Property Name="ModifiedDate" Type="DateTime" Nullable="false" />
          <NavigationProperty Name="Contact" 
Relationship="AdventureWorksModel.FK_SalesOrderHeader_Contact_ContactID"
            FromRole="SalesOrderHeader" ToRole="Contact" />
          <NavigationProperty Name="SalesOrderDetail"
Relationship="AdventureWorksModel.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
            FromRole="SalesOrderHeader" ToRole="SalesOrderDetail" />
        </EntityType>

        <EntityType Name="AddressType">
          <Key>
            <PropertyRef Name="AddressTypeID" />
          </Key>
          <!-- Other properties -->
        </EntityType>

        <EntityType Name="Contact">
          <Key>
            <PropertyRef Name="ContactID" />
          </Key>
          <!-- Other properties -->
          <NavigationProperty Name="SalesOrderHeader"
Relationship="AdventureWorksModel.FK_SalesOrderHeader_Contact_ContactID" 
            FromRole="Contact" ToRole="SalesOrderHeader" />
        </EntityType>

        <EntityType Name="Product">
          <Key>
            <PropertyRef Name="ProductID" />
          </Key>
          <!-- Other properties -->
        </EntityType>


        <Association Name="FK_SalesOrderHeader_Contact_ContactID">
          <End Role="Contact" 
              Type="AdventureWorksModel.Contact" Multiplicity="1" />
          <End Role="SalesOrderHeader"
        Type="AdventureWorksModel.SalesOrderHeader" Multiplicity="*" />
        </Association>

        <Association Name="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID">
          <End Role="SalesOrderHeader"
          Type="AdventureWorksModel.SalesOrderHeader" Multiplicity="1">
            <OnDelete Action="Cascade" />
          </End>
          <End Role="SalesOrderDetail"
           Type="AdventureWorksModel.SalesOrderDetail" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="SalesOrderHeader">
              <PropertyRef Name="SalesOrderID" />
            </Principal>
            <Dependent Role="SalesOrderDetail">
              <PropertyRef Name="SalesOrderID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
      </Schema>

For more information about CSDL, see Conceptual Schema (CSDL).

Stored Procedures used in Example

In order to demonstrate use of stored procedures, the following database scripts are provided to modify the AdventureWorks database. The scripts create stored procedures that create, update, and delete instances of SalesOrderDetail in storage.

Note

Transaction management within stored procedures used by the Entity Framework data is not recommended because it may conflict with the Entity Framework processing.

CreateSalesOrderDetail Procedure

The following script creates the stored procedure that adds SalesOrderDetail items to storage. The script contains code that can be used to delete the stored procedure if it is not needed after testing this example. To delete the stored procedure, omit the lines after drop procedure and run the script.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.CreateSalesOrderDetail', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.CreateSalesOrderDetail;
GO

CREATE PROCEDURE [dbo].[CreateSalesOrderDetail] 
   @SalesOrderID int,
   @CarrierTrackingNumber nvarchar(25),
   @OrderQty smallint,
   @ProductID int,
   @SpecialOfferID int,
   @UnitPrice money,
   @UnitPriceDiscount money,
   @rowguid uniqueidentifier,
   @ModifiedDate datetime
   
AS

INSERT INTO [AdventureWorks].[Sales].[SalesOrderDetail]
           ([SalesOrderID]
           ,[CarrierTrackingNumber]
           ,[OrderQty]
           ,[ProductID]
           ,[SpecialOfferID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           (@SalesOrderID,
           @CarrierTrackingNumber,
           @OrderQty,
           @ProductID,
           @SpecialOfferID,
           @UnitPrice,
           @UnitPriceDiscount,
           @rowguid,
           @ModifiedDate)

select SalesOrderDetailID, LineTotal
 from [AdventureWorks].[Sales].[SalesOrderDetail]
 where SalesOrderID = @SalesOrderID and SalesOrderDetailID = scope_identity()

UpdateSalesOrderDetail Procedure

The following script creates the stored procedure used to update SalesOrderDetail items in storage.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.UpdateSalesOrderDetail', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.UpdateSalesOrderDetail;
GO

CREATE PROCEDURE [dbo].[UpdateSalesOrderDetail]
   @OrderQty smallint, 
   @SalesOrderDetailID int,
   @SalesOrderID int

AS
UPDATE [AdventureWorks].[Sales].[SalesOrderDetail]
   SET [OrderQty] = @OrderQty
 WHERE SalesOrderDetailID = @SalesOrderDetailID

DeleteSalesOrderDetail Procedure

The following script creates the stored procedure used to delete SalesOrderDetail items in storage.

USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.DeleteSalesOrderDetail', 'P' ) IS NOT NULL 
DROP PROCEDURE dbo.DeleteSalesOrderDetail;
GO

CREATE PROCEDURE [dbo].[DeleteSalesOrderDetail] 
   @SalesOrderDetailID int,
   @SalesOrderID int 
AS
DELETE FROM [AdventureWorks].[Sales].[SalesOrderDetail]
      WHERE SalesOrderDetailID = @SalesOrderDetailID

Store Schema Definition Language (SSDL)

In the storage schema, Function elements define the stored procedures available in the database. Nested Parameter elements specify the names of the parameters of the stored procedures that can be mapped. These declarations inform the Entity Framework that stored procedures exist in the database but do not specify mapping. Mapping will be implemented in the mapping schema as shown later in this topic.

The IsComposable attribute of a function declaration representing a stored procedure must be set to false. This indicates that results returned by the procedure cannot be used in the FROM clause of other Entity SQL statements. The following declarations in the storage schema specify three stored procedures: CreateSalesOrderDetail, UpdateSalesOrderDetail, and DeleteSalesOrderDetail.

        <Function Name="CreateSalesOrderDetail" Aggregate="false"
                  BuiltIn="false" NiladicFunction="false"
                  IsComposable="false"
                  ParameterTypeSemantics="AllowImplicitConversion"
                  Schema="dbo">
          <Parameter Name="SalesOrderID" Type="int" Mode="In" />
          <Parameter Name="CarrierTrackingNumber" Type="nvarchar" Mode="In" />
          <Parameter Name="OrderQty" Type="smallint" Mode="In" />
          <Parameter Name="ProductID" Type="int" Mode="In" />
          <Parameter Name="SpecialOfferID" Type="int" Mode="In" />
          <Parameter Name="UnitPrice" Type="money" Mode="In" />
          <Parameter Name="UnitPriceDiscount" Type="money" Mode="In" />
          <Parameter Name="rowguid" Type="uniqueidentifier" Mode="In" />
          <Parameter Name="ModifiedDate" Type="datetime" Mode="In" />
        </Function>

        <Function Name="UpdateSalesOrderDetail" Aggregate="false"
                  BuiltIn="false" NiladicFunction="false"
                  IsComposable="false"
                  ParameterTypeSemantics="AllowImplicitConversion"
                  Schema="dbo">
          <Parameter Name="OrderQty" Type="smallint" Mode="In"/>
          <Parameter Name="SalesOrderDetailID" Type="int" Mode="In"/>
          <Parameter Name="SalesOrderID" Type="int" Mode="In"/>
        </Function>

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

For the complete storage schema of the AdventureWorks sales model as it exists before adding the stored procedures, see AdventureWorks Sales Storage Schema (EDM).

Mapping Specification Language (MSL)

The mapping specification defines the mapping between functions declared in the storage schema and stored procedures in the database.

Under the EntityTypeMapping element for an EntitySetMapping, the ModificationFunctionMapping element describes which functions specified in the SSDL file handle change processing. Child elements include DeleteFunction, InsertFunction, and UpdateFunction. Each function mapping specifies the FunctionName for the stored procedure being mapped.

The AssociationEnd element within the EntityTypeMapping allows you to treat a relationship as a reference, or foreign key, that is the basis of an association that relates the entity to another entity. For information about creating or deleting associations between existing entities by stored procedures, see Mapping Association Sets to Stored Procedures (Entity Framework).

  <ModificationFunctionMapping >
    <InsertFunction
       FunctionName="AdventureWorksModel.Store.CreateSalesOrderDetail">
        <ScalarProperty Name="CarrierTrackingNumber"
              ParameterName="CarrierTrackingNumber" Version="Current"/>
        <ScalarProperty Name="OrderQty" ParameterName="OrderQty"
              Version="Current"/>
        <ScalarProperty Name="ProductID" ParameterName="ProductID" Version="Current"/>
         <ScalarProperty Name="SpecialOfferID"
              ParameterName="SpecialOfferID" Version="Current"/>
        <ScalarProperty Name="UnitPrice" 
              ParameterName="UnitPrice" Version="Current"/>
        <ScalarProperty Name="UnitPriceDiscount"
              ParameterName="UnitPriceDiscount" Version="Current"/>
        <ScalarProperty Name="rowguid" ParameterName="rowguid"
              Version="Current"/>
        <ScalarProperty Name="ModifiedDate"
              ParameterName="ModifiedDate" Version="Current"/>
        <AssociationEnd
           AssociationSet="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
          From="SalesOrderDetail" To="SalesOrderHeader">
        <ScalarProperty Name="SalesOrderID"
                   ParameterName="SalesOrderID" />
        </AssociationEnd>
        <ResultBinding ColumnName="SalesOrderDetailID"
                   Name="SalesOrderDetailID" />
         <ResultBinding ColumnName="LineTotal" Name="LineTotal" />
  </InsertFunction>

    <UpdateFunction
    FunctionName="AdventureWorksModel.Store.UpdateSalesOrderDetail" >
        <ScalarProperty Name="OrderQty" ParameterName="OrderQty"
             Version="Current"/>
        <ScalarProperty Name="SalesOrderDetailID"
           ParameterName="SalesOrderDetailID" Version="Current"/>
        <AssociationEnd
    AssociationSet="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
       From="SalesOrderDetail" To="SalesOrderHeader">
          <ScalarProperty Name="SalesOrderID"
            ParameterName="SalesOrderID" Version="Current" />
        </AssociationEnd>
    </UpdateFunction>

    <DeleteFunction
     FunctionName="AdventureWorksModel.Store.DeleteSalesOrderDetail" >
        <ScalarProperty Name="SalesOrderDetailID"
           ParameterName="SalesOrderDetailID" Version="Original"/>
        <AssociationEnd
           AssociationSet="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID"
          From="SalesOrderDetail" To="SalesOrderHeader">
        <ScalarProperty Name="SalesOrderID"
                  ParameterName="SalesOrderID" />
        </AssociationEnd>
    </DeleteFunction>
  </ModificationFunctionMapping>

To examine the mapping schema for the AdventureWorks model before addition of stored procedures, see AdventureWorks Sales Mapping Schema (EDM).

Optimistic Concurrency Control

The Version attribute of the ScalarProperty element supports the use of optimistic concurrency control for updates and deletes. You can specify a Version attribute that has a value of either original, as originally read from the database, or current, potentially changed by client code. Specifying the version is required in the update function mappings. Specifying the version is optional for the delete function mappings. For inserts, no optimistic concurrency control is required because there are no original values to test against the data source.

Setting the Version attribute enables stored procedures to take both old and new values as parameters when performing optimistic concurrency control. It enables you to make sure that an update or deletion will occur only if the data source still holds the same values that the application last retrieved from the data source.

Input parameters bound to the original versions of entity properties are used in the WHERE clause of an UPDATE or DELETE statement in a stored procedure. For updates, additional parameters bound to the current versions of entity properties are used in the SET clause of an UPDATE statement in a stored procedure. This verifies that the new, current, values are assigned to the data source only when the original values still match the values in the data source. The update or deletion will fail if another user or another application has changed the values in the data source since the original values were retrieved.

Retrieving Server Values

For inserts and updates, an additional child element, ResultBinding, supports returning server-generated values through a result set. The ResultBinding element specifies how return values correspond to entity properties and enables the update pipeline to set values in objects based on the conceptual model.

The ResultBinding element has a Name attribute, which is the name of the property in the referenced entity definition, and a ColumnName attribute, which is the name of a column in the result set returned by the stored procedure. The ResultBinding element is shown in the following schema segment.

    <ResultBinding Name="SalesOrderDetailID"
                    ColumnName="SalesOrderDetailID" />
    <ResultBinding Name="LineTotal" ColumnName="LineTotal" />
  </InsertFunction>

In the stored procedure code, use a SELECT statement, after the INSERT or UPDATE statement has executed, to retrieve the value passed to ResultBinding.

SELECT SalesOrderDetailID, LineTotal
  FROM [AdventureWorks].[Sales].[SalesOrderDetail]
  WHERE SalesOrderID = @SalesOrderID and SalesOrderDetailID = scope_identity()

See Also

Tasks

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

Concepts

ModificationFunctionMapping (EntityTypeMapping)
ModificationFunctionMapping (AssociationSetMapping)
Entity Framework Resources
Entity Framework Terminology
Mapping Association Sets to Stored Procedures (Entity Framework)

Other Resources

EDM Specifications
Schemas and Mapping Specification (Entity Framework)
Walkthrough: Mapping an Entity to Stored Procedures