Stored Procedure Mapping
In this blog post, we’d like to walk through the EDM designer’s support for specifying insert, update, and delete stored procedures for entity types. Each of these stored procedure types has some non-obvious requirements and capabilities and we’d especially like to call those out here. We assume that you know why you want your types mapped to stored procedures and instead focus on the how. We also assume a small degree of familiarity with the EDM designer – you should know how to create a new entity data model edmx file from a database.
For this walkthrough, we are using a very simple product-orders SQL 2005 database. The tables look are:
CREATE TABLE [dbo].[Product](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[price] [float] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Order](
[id] [int] IDENTITY(1,1) NOT NULL,
[productId] [int] NOT NULL,
[quantity] [int] NOT NULL,
[timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Order] WITH CHECK ADD CONSTRAINT [FK_Order_Product] FOREIGN KEY([productId])
REFERENCES [dbo].[Product] ([id])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Product]
We also set up a number of stored procedures for managing the rows in these tables:
CREATE procedure [dbo].[Product_Insert](@name varchar(50),@price float) as
insert into Product([name], price) values (@name, @price)
select SCOPE_IDENTITY() as id
CREATE procedure [dbo].[Product_Update](@id int, @name varchar(50), @price float) as
update Product set [name] = @name, price = @price where id=@id
CREATE procedure [dbo].[Product_Delete](@id int) as
Delete from [Product] where id=@id
CREATE procedure [dbo].[Order_Insert](@productId int, @quantity int) as
insert into [Order] (productId, quantity) values(@productId, @quantity)
select SCOPE_IDENTITY() as id
CREATE procedure [dbo].[Order_Update](@id int, @productId int, @quantity int, @originalTimestamp timestamp) as
Update [Order] set quantity=@quantity
where id=@id and [timestamp]=@originalTimestamp
CREATE procedure [dbo].[Order_Delete](@id int, @productId int) as
Delete from [Order] where id=@id
Create a database and run the script against it. Then, add a new ADO.NET Entity Data Model item to a console application project. Generate a model from this database, including both the Product and Order tables and the six stored procedures.
Once you are done, your model should look like this:
In addition, if you expand your model browser’s conceptual and store schema nodes, you should see this:
Looking above, you can see the stored procedures that were added.
We are now ready to map these stored procedures to the appropriate actions. At this point it is appropriate to note the first stumbling block: At this time, the Entity Framework requires you to map all three stored procedures. You cannot map only the delete stored procedure. In addition, the entity type must still be mapped to a table, view, or query view.
We’ll begin with the simpler of the two types to map – the Product type. Right-click on the product type and bring up the mapping view. By default, the mapping view displays in the table mapping view. Click on the stored procedure mapping view icon, which is on the left side of the view and which looks like this: . In the soon-to-be-released version, you will also be able to right-click an entity type and select “Stored Procedure Mapping”.
Insert Product
Click on the <Select Insert Function> and the view should look like this:
Click on the drop-down button and select “Product_Insert”. At this point, the view should look like this:
Now let’s take a look at the stored procedure to which we are mapping:
CREATE procedure [dbo].[Product_Insert](@name varchar(50),@price float) as
insert into Product([name], price) values (@name, @price)
select SCOPE_IDENTITY() as id
When a product is inserted, the stored procedure returns the id that was created for the row. We need to write this id back to the product entity instance in our client. The way to do this is by using the result column bindings: Click once on <Add Result Binding>, type “id”, and hit the Tab or Enter key:
The value of the id column from the stored procedure’s return table will now be mapped back to the entity once the stored procedure completes.
Update Product
The is a straightforward mapping: We assign the Product_Update stored procedure to this task:
We will come back to the update stored procedure in more detail when we map the Order type.
Delete Product
This is also a simple mapping task: We assign the Product_Delete stored procedure to this task:
Now we move on to the more interesting of the types.
Insert Order
Inserting an order adds a small twist: We need to provide a productId, but the productId column is not surfaced in the entity. Luckily, we have our navigation property, so we can map the productId parameter to Product_1.id:
Note that we also map a result column binding to Order’s key property.
Delete Order
We will discuss the update stored procedure last, because I would like to run some code against it, which requires all stored procedures to be mapped. The delete stored procedure looks like this:
CREATE procedure [dbo].[Order_Delete](@id int, @productId int) as
Delete from [Order] where id=@id
Note that we have an unused parameter called @productId in this stored procedure. This is another subtle issue that you should be aware of: The runtime requires associations to be consistently mapped for all three stored procedure operations, and since we’ve mapped the product-orders relationship in the insert stored procedure, we must also map it here.
We hope to provide a solution for this issue in future versions of the Entity Framework, but for now, this is what needs to be done for associations whose target multiplicity, with respect to the type being mapped, is 1 or 0..1. In other words, the “reference” end of the association needs to be mapped to the keys of the target type.
As an alternative, it is possible to define a function in the SSDL which defines its own CommandText element and which declares two parameters, and then turns around and calls a single-parameter version of Order_Delete. However, this is not supported through the designer, and furthermore, the designer’s “Update Model from Database” feature currently regenerates the entire SSDL section of the EDMX file.
So, the mapping for the Order_Delete stored procedure looks like this:
Update Order
Here, for demonstration purposes, we add a small wrinkle: We’re going to use SQL Server’s timestamp datatype to provide us with the basis for a simple optimistic concurrency mechanism. The Order_Update stored procedure looks like this:
CREATE procedure [dbo].[Order_Update](@id int, @productId int, @quantity int, @originalTimestamp timestamp) as
Update [Order] set quantity=@quantity
where id=@id and [timestamp]=@originalTimestamp
The idea here is that we only update the order if the timestamp sent back to the database is identical to the timestamp that was originally retrieved from it, indicating that no other process has modified the Order in the meantime. In order to ensure that the original value is sent to the database, we make use ot the “use Original Value” checkbox, which you see at far right here:
Now that we’ve mapped the stored procedures for the model, we can write some code in the console application’s Program.cs file to test this optimistic concurrency behavior:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StoredProcsDemoModel;
namespace SprocsWalkthrough
{
class Program
{
private static StoredProcsDemoEntities context1 = null, context2 = null;
static void Main(string[] args)
{
try {
context1 = new StoredProcsDemoEntities();
context2 = new StoredProcsDemoEntities();
deleteExistingEntities();
createTestEntities();
causeConcurrencyConflict();
Console.Read();
}
finally {
if (context1 != null) context1.Dispose();
if (context2 != null) context2.Dispose();
}
}
//Clear the order and product tables of values so that we can run this code multiple times
//without worrying about data left over from previous runs
static void deleteExistingEntities()
{
foreach(Product p in context1.Product) {
context1.DeleteObject(p);
}
foreach(Order o in context1.Order) {
context1.DeleteObject(o);
}
context1.SaveChanges();
}
//Create a product and an order that is associated with it
static void createTestEntities()
{
Product product = new Product();
product.name = "My Product";
product.price = 100;
context1.AddToProduct(product);
Order order = new Order();
order.Product_1 = product;
order.quantity = 5;
context1.AddToOrder(order);
context1.SaveChanges();
Console.Out.WriteLine("Created a product with id " + product.id);
Console.Out.WriteLine("Created an order with id " + order.id);
}
//Finally, update the order from two separate context to simulation a concurrency
//exception.
static void causeConcurrencyConflict()
{
Order order = (from o in context1.Order select o).First();
Order sameOrder = (from o in context2.Order select o).First();
Console.Out.WriteLine("order id = " + order.id);
Console.Out.WriteLine("sameOrder id = " + sameOrder.id);
sameOrder.quantity++;
context2.SaveChanges();
order.quantity++;
try {
context1.SaveChanges();
}
catch (System.Data.OptimisticConcurrencyException oce) {
Console.Out.WriteLine(oce.Message);
Console.Out.WriteLine("The conflict occurred on "
+ oce.StateEntries[0].Entity + " with key value "
+ oce.StateEntries[0].EntityKey.EntityKeyValues[0].Value);
}
}
}
}
When we run this code, we see the following output:
Created a product with id 14
Created an order with id 9
order id = 9
sameOrder id = 9
Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
The conflict occurred on StoredProcsDemoModel.Order with key value 9
What the Entity Framework did automatically on our behalf was detect that that the number of rows affected by the last call to context1.SaveChanges() was 0. It then threw exactly the kind of exception we need, and in it put useful information about which entities were involved in the update.
(No rows were affected because the call call to context2.SaveChanges() changed the value of the timestamp column, meaning that Order_Update did not update any row in the database.)
We hope this post was useful to you. Please send us any questions or comments, or post on the ADO.NET forum to let us know your thoughts.
Noam Ben-Ami
Program Manager, ADO.NET Entity Framework Tools