Using Stored Procedures for Change Processing in the ADO.NET Entity Framework

 


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