Most people who’ve played with the ADO.NET Entity Framework eventually ask whether they can replace the SQL statements it generates with stored procedures of their own. This is important in many applications because direct table access is not allowed. Current builds of the Entity Framework support using stored procedures for inserting, updating, and deleting entities. We are actively working on supporting stored procedures for reading entities – this should be available in an upcoming build.

In this post, I cover the use of stored procedures for change processing: taking changes (inserts, updates, and deletes) made to entities at the object layer and propagating them to the database. The examples used in this post are based on the Orcas March CTP. Readers unfamiliar with the ADO.NET Entity Framework should first read the ADO.NET Entity Framework Overview before proceeding – in this post, I assume you already have a working knowledge of Entity Framework basics, including conceptual models, mapping, and programming with Object Services.  In addition to the overview, readers new to the Entity Framework may benefit from looking at the ADO.NET Samples for the Orcas October CTP.

Context: The Sample Database, Conceptual Model, Mapping, and Client Code

I’ll illustrate the use of stored procedures in the context of a sample application. For clarity, the database and conceptual model I use are exceedingly simple, but they are sufficient to illustrate the key concepts involved in working with stored procedures in the Entity Framework.

This section describes the sample database, the conceptual model and its mapping to the database, and the client code used to manipulate entities with the ADO.NET Entity Framework. At first, the operations on the entities will translate to direct insert, update, and delete statements against the tables. In the next section, we’ll look at how to change the mapping so that the Entity Framework calls a set of stored procedures to perform these operations, rather than manipulating the tables directly.

Logical Database Schema

Our database is called ProductInventory and has tables that encapsulate product and category information. The logical schema of this database is shown in Figure 1.

 

Figure 1: Logical schema of the ProductInventory database

The Categories table stores the information about known categories. The table has columns that store an auto-generated CategoryID (the primary key of this table) and a string CategoryName. The Products table stores product information: an auto-generated ProductID (the primary key of this table), a string ProductName, and a CategoryID (a foreign key into the Categories table). The CategoryID value for a given Product row specifies the category in which that product belongs.

My SSDL file reflects this logical schema, as shown in Listing 1 (attributes on the root <Schema> element have been omitted from this and subsequent listings for brevity; an ellipsis appears in place of the missing attributes):

<Schema ...>

  <EntityContainer Name="dbo">

    <EntitySet Name="Categories" EntityType="dbo.Categories" />

    <EntitySet Name="Products" EntityType="dbo.Products" />

    <AssociationSet Name="FK__Products__Catego__7F60ED59"

                    Association="dbo.FK__Products__Catego__7F60ED59">

      <End Role="Categories" EntitySet="Categories" />

      <End Role="Products" EntitySet="Products" />

    </AssociationSet>

  </EntityContainer>

 

  <EntityType Name="Categories" Key="CategoryID">

    <Property Name="CategoryID" Type="int" Nullable="false"

              StoreGeneratedPattern="identity" />

    <Property Name="CategoryName" Type="nvarchar" Nullable="false" />

  </EntityType>

 

  <EntityType Name="Products" Key="ProductID">

    <Property Name="ProductID" Type="int" Nullable="false"

              StoreGeneratedPattern="identity" />

    <Property Name="ProductName" Type="nvarchar" Nullable="false" />

    <Property Name="CategoryID" Type="int" Nullable="false" />

  </EntityType>

 

  <Association Name="FK__Products__Catego__7F60ED59">

    <End Role="Categories" Type="dbo.Categories" Multiplicity="1..1" />

    <End Role="Products" Type="dbo.Products" Multiplicity="*" />

    <ReferentialConstraint FromRole="Categories" ToRole="Products"

                           FromProperty="CategoryID" ToProperty="CategoryID" />

  </Association>

</Schema>

Listing 1: SSDL declaration of the ProductInventory logical schema

 

Conceptual Model

Because the purpose of this post is to illustrate stored procedures and not the richness of the Entity Framework’s mapping capabilities, I’ll use a conceptual model that is a simple 1:1 mapping to the logical database schema. That model is shown in Figure 2.

Figure 2: Conceptual model for products and categories

This model has two entity types: Category and Product. The properties of these entity types map to the columns in the Categories and Products tables respectively. The model also has an association type, Category_Product that describes a 1:* association relationship between the two entity types.

This conceptual model can be declared in CSDL as shown in Listing 2.

<Schema ...>

  <EntityContainer Name="ProductInventoryContainer">

    <EntitySet Name="Categories" EntityType="ProductInventory.Category" />

    <EntitySet Name="Products" EntityType="ProductInventory.Product" />

    <AssociationSet Name="Category_Products"

                    Association="ProductInventory.Category_Product">

      <End Role="Category" EntitySet="Categories" />

      <End Role="Product" EntitySet="Products" />

    </AssociationSet>

  </EntityContainer>

 

  <EntityType Name="Category" Key="CategoryID">

    <Property Name="CategoryID" Type="Int32" Nullable="false" />

    <Property Name="CategoryName" Type="String" Nullable="false"

              MaxLength="4000" />

    <NavigationProperty Name="Products"

                        Relationship="ProductInventory.Category_Product"

                        FromRole="Category" ToRole="Product" />

  </EntityType>

 

  <EntityType Name="Product" Key="ProductID">

    <Property Name="ProductID" Type="Int32" Nullable="false" />

    <Property Name="ProductName" Type="String" Nullable="false"

              MaxLength="4000" />

    <NavigationProperty Name="Category"

                        Relationship="ProductInventory.Category_Product"

                        FromRole="Product" ToRole="Category" />

  </EntityType>

 

  <Association Name="Category_Product">

    <End Role="Category" Type="ProductInventory.Category" Multiplicity="1..1" />

    <End Role="Product" Type="ProductInventory.Product" Multiplicity="*" />

  </Association>

</Schema>

Listing 2: CSDL declaration of the conceptual model

Mapping

The conceptual model can be mapped 1:1 to the logical database schema via the MSL declaration shown in Listing 3. Here, each entity set and association set is mapped to the corresponding tables in the underlying database.

 

<Mapping cs:Space="C-S" ...>

  <cs:EntityContainerMapping cs:StorageEntityContainer="dbo"

                             cs:CdmEntityContainer="ProductInventoryContainer">

   

    <cs:EntitySetMapping cs:Name="Categories">

      <cs:EntityTypeMapping cs:TypeName="ProductInventory.Category">

        <cs:TableMappingFragment cs:TableName="Categories">

          <cs:ScalarProperty cs:Name="CategoryID" cs:ColumnName="CategoryID" />

          <cs:ScalarProperty cs:Name="CategoryName"

                             cs:ColumnName="CategoryName" />   

        </cs:TableMappingFragment>               

      </cs:EntityTypeMapping>     

    </cs:EntitySetMapping>

   

    <cs:EntitySetMapping cs:Name="Products">

      <cs:EntityTypeMapping cs:TypeName="ProductInventory.Product">

        <cs:TableMappingFragment cs:TableName="Products">

          <cs:ScalarProperty cs:Name="ProductID" cs:ColumnName="ProductID" />

          <cs:ScalarProperty cs:Name="ProductName"

                             cs:ColumnName="ProductName" />

        </cs:TableMappingFragment>

               

      </cs:EntityTypeMapping>           

    </cs:EntitySetMapping>

   

    <cs:AssociationSetMapping cs:Name="Category_Products"

                              cs:TypeName="ProductInventory.Category_Product"

                              cs:TableName="Products">

      <cs:EndProperty cs:Name="Category">

        <cs:ScalarProperty cs:Name="CategoryID" cs:ColumnName="CategoryID" />

      </cs:EndProperty>

      <cs:EndProperty cs:Name="Product">

        <cs:ScalarProperty cs:Name="ProductID" cs:ColumnName="ProductID" />

      </cs:EndProperty>

      <cs:Condition cs:ColumnName="CategoryID" cs:IsNull="false" />

    </cs:AssociationSetMapping>

 

  </cs:EntityContainerMapping>

</Mapping>

Listing 3: MSL declaration of the mapping from the conceptual model to the logical database schema

Client Code

With the database, model, and mapping in place, it’s time to look at some client code that reads and manipulates entities using the Entity Framework’s Object Services. Listing 4 shows an example of code that obtains an entity container, adds a new category and two new products, then changes one of the products, and finally deletes the category and both products.

// Obtain an context object for the ProductInventory entity container.

using (ProductInventoryContainer container = new ProductInventoryContainer())

{

    // Create a new category.

    Category hardware = new Category();

    hardware.CategoryName = "Hardware";

    container.AddObject(hardware);

 

    // Create some products in the new category.

    Product hammer = new Product();

    hammer.ProductName = "Hammer";

    hammer.Category = hardware;

    container.AddObject(hammer);

 

    Product screwDriver = new Product();

    screwDriver.ProductName = "Screwdriver";

    screwDriver.Category = hardware;

    container.AddObject(screwDriver);

 

    // Persist changes.

    container.SaveChanges();

 

    // Change one of the products.

    screwDriver.ProductName = "Electric Screwdriver";                              

    container.SaveChanges();

 

    // Remove the hardware category and all its products.

    container.DeleteObject(screwDriver);

    container.DeleteObject(hammer);

    container.DeleteObject(hardware);

 

    container.SaveChanges();

}

Listing 4: Client code that uses ADO.NET Object Services to read and manipulate entities

If we examine the SQL that the Entity Framework generates and sends to the server when the SaveChanges() method is called, we see that inserts, updates, and deletes are performed directly against the database tables. Listing 5 shows the SQL produced for the operations performed in this code sample. Don’t worry too much about the details of this SQL – just notice that the operations are performed directly on the tables.

exec sp_executesql N'insert [dbo].[Categories]([CategoryName])

values (@0)

select [CategoryID]

from [dbo].[Categories]

where @@ROWCOUNT > 0 and [CategoryID] = scope_identity()',N'@0 nvarchar(8)',@0=N'Hardware'

 

exec sp_executesql N'insert [dbo].[Products]([ProductName], [CategoryID])

values (@0, @1)

select [ProductID]

from [dbo].[Products]

where @@ROWCOUNT > 0 and [ProductID] = scope_identity()',N'@0 nvarchar(6),@1 int',@0=N'Hammer',@1=7

 

exec sp_executesql N'insert [dbo].[Products]([ProductName], [CategoryID])

values (@0, @1)

select [ProductID]

from [dbo].[Products]

where @@ROWCOUNT > 0 and [ProductID] = scope_identity()',N'@0 nvarchar(11),@1 int',@0=N'Screwdriver',@1=7

 

exec sp_executesql N'update [dbo].[Products]

set [ProductName] = @0

where ([ProductID] = @1)

',N'@0 nvarchar(20),@1 int',@0=N'Electric Screwdriver',@1=18

 

exec sp_executesql N'delete [dbo].[Products]

where (([ProductID] = @0) and ([CategoryID] = @1))',N'@0 int,@1 int',@0=18,@1=7

 

exec sp_executesql N'delete [dbo].[Products]

where (([ProductID] = @0) and ([CategoryID] = @1))',N'@0 int,@1 int',@0=17,@1=7

 

exec sp_executesql N'delete [dbo].[Categories]

where ([CategoryID] = @0)',N'@0 int',@0=7

Listing 5: SQL generated by the Entity Framework to perform inserts, updates, and deletes on the database tables

In the following section, we’ll define stored procedures to perform the insert, update, and delete operations. We’ll then change the mapping to instruct the Entity Framework to use these stored procedures rather than manipulating the tables directly.

Using Stored Procedures to Manipulate Entities

In this section, we’ll change our application to use stored procedures to manipulate Category entities. We start with Category because it the simpler of the two entity types and will not require us to think about relationships yet. In the next section, we’ll look at using stored procedures when relationships are involved.

Stored Procedure Definitions

Let’s create stored procedures in the database for creating a new category, updating a category, and deleting a category. Listing 6 shows the definition of these procedures.

CREATE PROC [CreateCategory](

       @CategoryName NVARCHAR(255)

)

AS

BEGIN

       -- Insert the category data.

       INSERT INTO [Categories](CategoryName) VALUES (@CategoryName)

      

       -- Get back the id of the newly created category.

       SELECT CategoryID

       FROM [Categories]

       WHERE @@ROWCOUNT > 0 and CategoryID = scope_identity()

END

 

CREATE PROC [EditCategory](

       @CategoryID INT,

       @CategoryName NVARCHAR(255))

AS

UPDATE [Categories]

SET CategoryName=@CategoryName

WHERE CategoryID=@CategoryID

CREATE PROC [RemoveCategory](

       @CategoryID INT)

AS

DELETE [Categories]

WHERE CategoryID=@CategoryID

Listing 6: Definition of change processing stored procedures for the Categories table

The CreateCategory stored procedure inserts a new row into the Categories table and retrieves the auto-generated CategoryID. The EditCategory procedure enables changing a category name by updating the Categories table for a given CategoryID. The RemoveCategory procedure deletes a category by removing the row with a given CategoryID from the Categories table.

Stored Procedure Declaration in SSDL

In order to use these stored procedures within the Entity Framework, they must be declared in the SSDL file that describes the database’s logical schema. Listing 7 shows the SSDL snippet that declares the three Categories-related stored procedures.

<Schema>

   ...

  <Function Name="CreateCategory" IsComposable="false">

    <Parameter Name="CategoryName" Type="nvarchar" />   

  </Function>

 

  <Function Name="EditCategory" IsComposable="false">

    <Parameter Name="CategoryID" Type="int" />   

    <Parameter Name="CategoryName" Type="nvarchar" />   

  </Function>

 

  <Function Name="RemoveCategory" IsComposable="false">   

    <Parameter Name="CategoryID" Type="int" />   

  </Function>

</Schema>

Listing 7: SSDL declaration of stored procedures related to modifying the Categories table

These SSDL declarations simply describe the procedure names and parameters. Notice that the same <Function> SSDL element is used to declared stored procedures and user-defined functions. Stored procedures are distinguished from functions by the IsComposable attribute being set to false.

Mapping the Stored Procedures

Declaring the stored procedures in the SSDL file simply lets the Entity Framework know they exist in the database. But the SSDL declaration does not tell Entity Framework how and when to use these stored procedures. For that, we need to go back to the MSL file and change the declaration of the entity set mapping for categories. Listing 8 shows the MSL changes required (the boldface elements are new):

<Mapping cs:Space="C-S" ...>

  <cs:EntityContainerMapping cs:StorageEntityContainer="dbo"

                             cs:CdmEntityContainer="ProductInventoryContainer">

    <cs:EntitySetMapping cs:Name="Categories">

      <cs:EntityTypeMapping cs:TypeName="ProductInventory.Category">

        <cs:TableMappingFragment cs:TableName="Categories">

          <cs:ScalarProperty cs:Name="CategoryID" cs:ColumnName="CategoryID" />

          <cs:ScalarProperty cs:Name="CategoryName"

                             cs:ColumnName="CategoryName" />   

        </cs:TableMappingFragment>               

       

        <cs:ModificationFunctionMapping>

          <cs:InsertFunction cs:FunctionName="dbo.CreateCategory">

            <cs:ScalarProperty cs:Name="CategoryName"

                               cs:ParameterName="CategoryName" />           

            <cs:ResultBinding cs:ColumnName="CategoryID" cs:Name="CategoryID"/>

          </cs:InsertFunction>

       

          <cs:UpdateFunction cs:FunctionName="dbo.EditCategory">

            <cs:ScalarProperty cs:Name="CategoryID"

                               cs:ParameterName="CategoryID"

                               cs:Version="current"/>

            <cs:ScalarProperty cs:Name="CategoryName"

                               cs:ParameterName="CategoryName"

                               cs:Version="current"/>           

          </cs:UpdateFunction>

         

          <cs:DeleteFunction cs:FunctionName="dbo.RemoveCategory">

            <cs:ScalarProperty cs:Name="CategoryID"

                               cs:ParameterName="CategoryID"/>

          </cs:DeleteFunction>

        </cs:ModificationFunctionMapping>

       

      </cs:EntityTypeMapping>     

    </cs:EntitySetMapping>

    ...

  </cs:EntityContainerMapping>

</Mapping>

Listing 8: Changes to the Categories entity set mapping to enable stored procedures

Within the <EntityTypeMapping> element for Category, a new <ModificationFunctionMapping> element is added. In this element we specify the functions to use for inserts, updates, and deletes (note that we say “function” instead of “stored procedure” following the convention, established in the SSDL, of treating  stored procedures as non-composable functions).

For each function, we specify the function name and then mappings from the entity type’s properties to the function parameters. In the case of the insert function, we also specify a result binding: a mapping from columns in the stored procedure’s result set to the entity’s properties. This allows the Entity Framework to obtain the values of server-generated values, like the CategoryID in our example.

The property-parameter mappings for the update and delete functions can specify a Version attribute with a value of either “original” or “current”. In the update function mappings, Version is required; in the delete function mappings, it is optional. The Version attribute specifies whether the parameter is mapped to the original value of the entity’s property (as originally read from the database and maintained by the Object Services change tracking facilities) or the current value (potentially changed by the client code in-memory). This mechanism allows stored procedures to take both old and new values as parameters; this can be useful when performing optimistic concurrency control.  

A NOTE ABOUT OPTIMISTIC CONCURRENCY CONTROL: The examples in this document do not illustrate performing optimistic concurrency control within stored procedures. The mechanism for passing original and changed values of parameters (described in this section) allows for optimistic concurrency control checks to be performed within a stored procedure’s implementation. The Entity Framework determines whether a concurrency violation occurred by examining the number of rows affected by the stored procedure’s execution: a non-zero number of rows affected means the operation succeeded; zero indicates a concurrency violation.

With the mappings shown in Listing 8 in place, the Entity Framework will translate insert, update, and delete operations performed on Category entities through Object Services into calls to the declared stored procedures. At the end of the next section (in which we’ll look at stored procedure mappings for Product entities), we’ll examine the SQL trace to see these procedures being invoked.

Using Stored Procedures to Manipulate Entities and Relationships

In mapping the Product entities to stored procedures for change processing, we need to think about relationships. In our conceptual model, Product entities are related to Category entities via the Product_Category  relationship. In the database, this relationship is implemented with a foreign key from the product table to a categories table.

In terms of the logical database schema, we can think of the CategoryID foreign key like any other property of the product row. But in the EDM, relationships are first class constructs: a row in the Products table represents not just a Product entity but also an instance of the Product_Category relationship. When I delete a row from the Products table, I am removing two things that Object Services tracks: an entity and a relationship. If I update a Products table row and change only the value of the CategoryID column then I am not really updating a Product entity: I am deleting one Product_Category relationship and replacing it with a new one.

Relationships in the EDM can take a little time to get one’s head around. While this may seem abstract, it’s import to understand how the EDM treats relationships in order to make sense of what’s required to map stored procedures that manipulate them.  As we look at the stored procedures that do change processing for Product entities and the corresponding MSL declarations, this will probably become clearer.

Stored Procedure Definitions

Listing 9 shows the stored procedures we’ll create in the database to support change processing for Product entities:

CREATE PROC [CreateProduct](

       @ProductName NVARCHAR(255),

       @CategoryID INT)

AS

BEGIN

    -- Insert the product data

       INSERT INTO [Products](ProductName, CategoryID)

       VALUES (@ProductName, @CategoryID)

      

       -- Get back the id of the newly created product.

       SELECT ProductID

       FROM [Products]

       WHERE @@ROWCOUNT > 0 and ProductID = scope_identity()

END

CREATE PROC [EditProduct](

       @ProductID INT,

       @ProductName NVARCHAR(255),

       @CategoryID INT)

AS

UPDATE [Products]

SET ProductName=@ProductName, CategoryID=@CategoryID

WHERE ProductID=@ProductID

 

CREATE PROC [RemoveProduct](

       @ProductID INT,

       @CategoryID INT)

AS

DELETE [Products]

WHERE ProductID=@ProductID

Listing 9: Stored procedures for creating, editing, and removing rows in the Products table

These follow much the same patterns as the stored procedures we defined for the Categories table in Listing 6. The one noteworthy point here is that the RemoveProduct stored procedure takes both ProductID and CategoryID parameters but only uses the ProductID parameter. The CategoryID parameter could certainly be used to implement optimistic concurrency control within the procedure, but for this example, it is present in order to support the mapping required to handle the Category_Product relationship (shown in the next section).

Like before, we need to declare these stored procedures in the SSDL file in order to expose them to the Entity Framework. Listing 10 shows the required declarations.

<Schema>

   ...

  <Function Name="CreateProduct" IsComposable="false">

    <Parameter Name="ProductName" Type="nvarchar" />

    <Parameter Name="CategoryID" Type="int" />   

  </Function>

 

  <Function Name="EditProduct" IsComposable="false">

    <Parameter Name="ProductID" Type="int" />   

    <Parameter Name="ProductName" Type="nvarchar" />

    <Parameter Name="CategoryID" Type="int" />   

  </Function>

 

  <Function Name="RemoveProduct" IsComposable="false">   

    <Parameter Name="ProductID" Type="int" />   

    <Parameter Name="CategoryID" Type="int" />

  </Function>

 </Schema>

Listing 10: SSDL declaration of stored procedures related to modifying the Categories table

Mapping the Stored Procedures

After declaring the stored procedures in the SSDL file, we need to map them in the MSL file. Listing 11 shows the required changes to the mapping of the Products entity set:

<Mapping cs:Space="C-S" ...>

  <cs:EntityContainerMapping cs:StorageEntityContainer="dbo"

                             cs:CdmEntityContainer="ProductInventoryContainer">

    ...

    <cs:EntitySetMapping cs:Name="Products">

      <cs:EntityTypeMapping cs:TypeName="ProductInventory.Product">

        <cs:TableMappingFragment cs:TableName="Products">

          <cs:ScalarProperty cs:Name="ProductID" cs:ColumnName="ProductID" />

          <cs:ScalarProperty cs:Name="ProductName"

                             cs:ColumnName="ProductName" />

        </cs:TableMappingFragment>

               

        <cs:ModificationFunctionMapping>

          <cs:InsertFunction cs:FunctionName="dbo.CreateProduct">

            <cs:ScalarProperty cs:Name="ProductName"

                               cs:ParameterName="ProductName" />

            <cs:AssociationEnd cs:AssociationSet="Category_Products"                                 

                               cs:From="Product" cs:To="Category">

              <cs:ScalarProperty cs:Name="CategoryID"

                                 cs:ParameterName="CategoryID"/>

            </cs:AssociationEnd>

            <cs:ResultBinding cs:ColumnName="ProductID" cs:Name="ProductID"/>

          </cs:InsertFunction>

       

          <cs:UpdateFunction cs:FunctionName="dbo.EditProduct">

            <cs:ScalarProperty cs:Name="ProductID" cs:ParameterName="ProductID"

                               cs:Version="current"/>

            <cs:ScalarProperty cs:Name="ProductName"

                               cs:ParameterName="ProductName"

                               cs:Version="current"/>

            <cs:AssociationEnd cs:AssociationSet="Category_Products"

                               cs:From="Product" cs:To="Category">

              <cs:ScalarProperty cs:Name="CategoryID"

                                 cs:ParameterName="CategoryID"

                                 cs:Version="current" />

            </cs:AssociationEnd>

          </cs:UpdateFunction>

         

          <cs:DeleteFunction cs:FunctionName="dbo.RemoveProduct">

            <cs:ScalarProperty cs:Name="ProductID"

                               cs:ParameterName="ProductID"/>

            <cs:AssociationEnd cs:AssociationSet="Category_Products"

                               cs:From="Product" cs:To="Category">

              <cs:ScalarProperty cs:Name="CategoryID"

                                 cs:ParameterName="CategoryID"

                                 cs:Version="original" />

            </cs:AssociationEnd>

          </cs:DeleteFunction>

        </cs:ModificationFunctionMapping>       

      </cs:EntityTypeMapping>           

    </cs:EntitySetMapping>

  </cs:EntityContainerMapping>

</Mapping>

Listing 11: Changes to the Products entity set mapping to enable stored procedures

In many ways, the modification function mappings look similar to those in the previous example (Listing 8). The only substantial difference is in the way the stored procedures’ CategoryID parameters are mapped. Rather than mapping directly to a property of the Product entity type, these parameters are mapped to an association end in the Category_Products association set. Specifically, they are mapped to the CategoryID property of the Category end of the association.

This declaration informs the Entity Framework  that the CategoryID parameters refer to an end of the relationship. The Entity Framework uses this information to obtain the parameter values from the relationship instances that it tracks.

Because the CategoryID parameter is not used within the body of the RemoveProduct stored procedure, it’s natural to wonder why it needs to be declared and mapped. The reason is that the Entity Framework needs to know that the stored procedure deletes both a Product entity and a Category_Product relationship. The mapping of the CategoryID parameter to the relationship end lets the Entity Framework know it must call the stored procedures in the correct order to maintain the foreign key constraints in the database. Without this information, when several products and categories have been deleted via Object Services, the Entity Framework might generate the stored procedure calls to RemoveProduct and RemoveCategory in the wrong order, resulting in an attempt to remove a category before related products are deleted.

Stored Procedures in Action

With all the changes to the database, SSDL files, and MSL files described in the previous sections, we can now run the original client code (Listing 4) again to see the result. Note that we need not make any changes to the code – from the Object Services perspective, the operations don’t change. It is simply the modified mapping that results in the stored procedures being invoked, instead of the direct table manipulations. Listing 12 shows the results:

exec [dbo].[CreateCategory] @CategoryName=N'Hardware'

exec [dbo].[CreateProduct] @ProductName=N'Screwdriver',@CategoryID=8

exec [dbo].[CreateProduct] @ProductName=N'Hammer',@CategoryID=8

exec [dbo].[EditProduct] @ProductID=19,

                         @ProductName=N'Electric Screwdriver',@CategoryID=8

exec [dbo].[RemoveProduct] @ProductID=20,@CategoryID=8

exec [dbo].[RemoveProduct] @ProductID=19,@CategoryID=8

exec [dbo].[RemoveCategory] @CategoryID=8

Listing 12: Stored procedure calls generated by the Entity Framework to perform inserts, updates, and deletes

As this trace shows, the operations performed in Object Services translate to the stored procedures we declared.

I hope this post has been helpful and look forward to your feedback through comments.

Thanks

Shyam Pather, Development Lead – ADO.NET Entity Framework