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
Comments
Anonymous
March 27, 2008
L'ADO .Net Team vient de poster deux nouveaux posts : le premier concerne l'utilisation des procéduresAnonymous
March 29, 2008
Noam Ben-Ami, from the EF Tools team shows how to use the Entity Framework Designer tools to map StoreAnonymous
March 31, 2008
After attempting a similar example on my own and I get the following exception: Error 2027: If an entity set or association set includes a function mapping, all related entity and association sets in the entity container must also define function mappings. The following sets require function mappings: Report2Queries. THe Report2Queries is an 1 to many association between a Report and its queries.Anonymous
April 01, 2008
Tony, I assume that you have a report entity and a query entity, and that a report can contain many queries. I also assume that the query table has a foreign key to the report entity, something like reportId. Finally, I assume that your query entity has a navigation property, probably called report, which means that the insert, update, and delete stored procedures must have a parameter called reportId, as detailed in the blog entry. Given all of that, you need to map report.reportId on the query type to the reportId parameter on the insert, update, and delete stored procedures.Anonymous
April 16, 2008
>>>The value of the id column from the stored procedure’s return table will now be mapped back to the entity... Good article, thanks! I have some quetions:
- Is it possible map back to the entity a out(inout)-parameter from stored procedure?
- Is it possible map to a parameter of stored procedure any variable or property from other object (for example, I need to pass a ticketID)
Anonymous
April 22, 2008
I've a problem with using Stored proc in Entity Framework Model. For simple Example, I've 2 tables (and Entities), Prices (PriceID primary key, CategoryID, price) and Category (CategoryID primary key, CategoryName). I've relation FK_Prices_Category I was made EDM with stored proc - all OK. But I can't make Deleting prom Price entity. My code: 01 VmzkoPricesEntities ctx = new VmzkoPricesEntities(); 02 VmzkoPricesModel.Prices pr = ctx.GetPriceByID(newsID).First<VmzkoPricesModel.Prices>(); 03 ctx.DeleteObject(pr); 04 ctx.SaveChanges(true); GetPriceByID - is an imported stored proc with output of all fields of Prices (PriceID, CategoryID, price) BUT if I expand 'pr' object (row 02) then property Category is NULL. After that deleting is not available: Entities in 'VmzkoPricesEntities.Prices' participate in the 'FK_Prices_Category' relationship. 0 related 'Category' were found. Between 1 and 1 'Category' are expected. How can I fill Category property inside Prices object by using stored proc??? How can I fill that by alternative method?Anonymous
May 19, 2008
Hi Noam, Do you plan to implement a wizard that will be able to port (not just map) stored procedures with a non-trivial logic to LINQ code? The purpose is to run these converted stored procedures using EF approach against another data source which does not contain any logic but just data.Anonymous
August 23, 2008
Con un proyecto donde estoy probando Entity Framework , para ir mas alla de lo simple a lo que estamosAnonymous
October 15, 2008
The comment has been removedAnonymous
October 30, 2008
Definitely frustrated by limitations myself as well. I simply want to get a collection of read only entities from a table (a list of US States) using a stored procedure. Issues are: I must map the entity to a store table or view, even when updates are not going to be performed. The entity must map all primary keys to the store table, even though I only require a portion of the table values returned. Clearly the read only use case was not focused on in the design much. As a result, EF seems to tie my data access to the physical store more so than ever. It's interesting that this is so poor because it works fairly well in Linq to SQL except that I cannot specify a name for the return type or its properties. I have to live with the autogenerated return type. Please take this use case into account in the next release.Anonymous
December 20, 2008
如果对于sp在EDM的映射有一些了解的话，可以很清楚的明白，在EDM中映射一个sp返回的类型包括：Entity Type，Scalars Type,None；这三中类型分别对应的sp返回内容对应到数据库，表的层面就是：（Entity）一张表的所有字段或若干字段(必须包括主键）;（Scalars）返回一个常量（int, varchar）可以是数据库的一个字段，可以是sum,count,left...等函数操作的结果;(none)自然是什么都不返回.这里自然就有一个严重的问题，对于返回（multipleAnonymous
December 30, 2008
I got a similar error 2027 to Tony, and provided mappings for the association set. However, in my case, the relationship is 1 to 0..1 and when I mapped the Key of the dependent table to the PK of the independent table in all three Function mappings (as instructed), I got a slightly different error: Error 2027: If an EntitySet or AssociationSet includes a function mapping, all related entity and AssociationSets in the EntityContainer must also define function mappings. It went on to say that the Independent table required function mappings, even though up to now I was able to save changes using the regular .SaveChanges Method. Is this because of the 1 to 0..1 relationship?Anonymous
January 19, 2009
Olahhhh.. Amigo... I Cannot Run.. I got some error.. like this : error 2037: A mapping function bindings specifies a function EDMXModel.Store.Order_Insert but does not map the following function parameters: productId. 5 Ado.Net Team Reference.App_Code.Model.msl(61,6) : error 2048: The EntitySet 'Order' includes function mappings for AssociationSet 'FK_Order_Product', but none exists in element 'InsertFunction' for type 'EDMXModel.Order'. AssociationSets must be consistently mapped for all operations.Anonymous
January 19, 2009
I fixed it now... Thank you very much..for the article.Anonymous
January 23, 2009
The comment has been removedAnonymous
April 28, 2009
what is the point of mapping all the stored procedures with all the insert/update/delete? what if i don't do it? what will happen?Anonymous
April 28, 2009
The comment has been removedAnonymous
April 28, 2009
The comment has been removedAnonymous
September 11, 2009
Thanks Man. I was searching for the meaning of this error for 2 days.. Hope I can try and resolve it now.Anonymous
March 01, 2010
Dear Noam Ben-Ami, I tried the VS 2010 RC1 and the problem with mapping Delete procedures persists. When do you intend to provide the solution for this issue? RegardsAnonymous
March 04, 2010
I'm using VS2008 and just prototyping a possible Entity Framework solution for a brand new project. But hey ... our DBA insists on using Stored Procedures for EVERYTHING -- and that includes SELECT's. Remember them? Is there a way to map the Entity Framework to Select-based Stored Procedures? If not, WHY NOT? Also ... this company does not even want us being able to "see" the Tables from our app's. Remember ... that's one of the benefits of Stored Procedures .... As far as I can see, ya'll require us to have TABLE MAPPINGS, even if we use Stored Procedures, right? PLEASE GIVE US A SOLUTION TO THIS. As it is now, I don't see how the management here is going to buy-off on Entity Framework for this new project ... even though I'd like to use it.Anonymous
March 04, 2010
muy buen articulo me saco de muchas dudas q tenia muchas graciasAnonymous
October 11, 2010
I've have been trying to use ADO .Net Entity Data Model with Viual Studio 2010 (.Net 4). When I create the Entity Model and map Insert, Update, Delete Sprocs to Function I keep getting the error: Error 2098: A function mapping for 'to' role <Entity Name> is not permitted because it is a foreign key association. I thought that the issue was resolved in this version of .net 4. Any ideas on how to resolve this issue?Anonymous
October 11, 2010
I've have been trying to use ADO .Net Entity Data Model with Viual Studio 2010 (.Net 4). When I create the Entity Model and map Insert, Update, Delete Sprocs to Function I keep getting the error: Error 2098: A function mapping for 'to' role <Entity Name> is not permitted because it is a foreign key association. I thought that the issue was resolved in this version of .net 4. Any ideas on how to resolve this issue?Anonymous
October 12, 2011
Our insert procedures return values from SELECT statement with columns and values. How can I map these to the "Result Column Bindings"? I could not find anyway. HELP!!! Is there any way to work around? Thanks.Anonymous
January 01, 2013
Now add an additional stored procedure to the DB and try to persuade EDM to see it - good luck.Anonymous
February 18, 2014
The comment has been removedAnonymous
February 19, 2014
@logesh - RAISERROR has some nuances and will only throw when you enumerate beyond certain result sets (which EF is not doing) - see this for some more info stackoverflow.com/.../very-tricky-ado-net-situation-that-does-not-throw-an-error-but-should. Could you use the SQL THROW command instead, that will give you an exception.Anonymous
February 26, 2014
Your example is missing the one part that I need: in createTestEntities() you have the statement: context1.AddToProduct(product); I need to know where AddToProduct() comes from and how it became a method of the context.Anonymous
February 26, 2014
@Bruce Patin - This blog post is pretty outdated now (6 years old)... if you are using the newer code generation templates you probably want context1.Products.Add(...).