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

Thursday, March 08, 2007 5:31 PM by dpblogs

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

<AssociationSet Name="FK__Products__Catego__7F60ED59"

Association="dbo.FK__Products__Catego__7F60ED59">

</AssociationSet>

</EntityContainer>

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

StoreGeneratedPattern="identity" />

</EntityType>

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

StoreGeneratedPattern="identity" />

</EntityType>

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

<AssociationSet Name="Category_Products"

Association="ProductInventory.Category_Product">

</AssociationSet>

</EntityContainer>

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

MaxLength="4000" />

<NavigationProperty Name="Products"

Relationship="ProductInventory.Category_Product"

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

</EntityType>

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

MaxLength="4000" />

<NavigationProperty Name="Category"

Relationship="ProductInventory.Category_Product"

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

</EntityType>

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

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

)

BEGIN

-- Insert the category data.

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

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

ADO.NET team blog

Tags

Archives

MSDN

Other Data Programmability Team Blogs

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

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

Logical Database Schema

Using Stored Procedures to Manipulate Entities