Share via


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

The Entity Framework enables you to specify stored procedures to be used when modifying entity data. These stored procedures replace the methods generated by the Entity Framework. Stored procedures are called implicitly, so no changes are required to the data model defined in the conceptual schema or existing application code. For more information, see Stored Procedure Support (Entity Framework).

This topic shows how to define modification stored procedures for the SalesOrderDetail entity type in the Adventure Works Sales Model. To use 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).

To define modification stored procedures for the SalesOrderDetail entity type

  1. Execute the following Transact-SQL scripts to create the modification stored procedures in the AdventureWorks database:

    • CreateSalesOrderDetail procedure

      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

      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

      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 
      
  2. Add the following function definitions to the top level Schema element of the SSDL schema file for AdventureWorks Sales storage model:

            <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> 
    
  3. Insert the following XML fragment within the EntityTypeMapping element for the SalesOrderDetail in the EntitySetMapping. Do this in the MSL mapping file for AdventureWorks Sales storage model.

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

See Also

Concepts

Mapping Association Sets to Stored Procedures (Entity Framework)

Other Resources

Defining Advanced Data Models (Entity Framework Tasks)