By: Ray Barley | Comments (3) | Related: > Entity Framework
Problem
I'm a DBA and the developers in my organization want to use the Entity Framework (EF) in their applications. While I understand the benefits of EF and the fact that you don't need stored procedures, we have many existing stored procedures and I would like to be able to continue to use them for insert, update and delete operations. How can I do this with the Entity Framework?
Solution
This is a common question about EF and the answer is that leveraging stored procedures for insert, update and delete is actually a built-in capability of EF. If you choose to used stored procedures for a particular entity, you have to provide an insert, update and a delete stored procedure; you can't mix and match stored procedures and the automatically generated SQL provided by EF. In addition your stored procedure parameters must match up exactly with the properties of the entity; the big caveat here is that you cannot have additional parameters that do not match up to a property in the entity.
To demonstrate let's start out with a very simple EF model with a single entity which represents the Tip Category table on the MSSQLTips web site:
We'll use the following stored procedures for insert, update and delete for our TipCategory entity:
CREATE PROCEDURE InsertTipCategory @TipCategoryName nvarchar(50) AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.TipCategory (TipCategoryName) VALUES (@TipCategoryName) SELECT CAST(SCOPE_IDENTITY() AS INT) AS NewTipCategoryID WHERE @@ROWCOUNT > 0 END GO CREATE PROCEDURE UpdateTipCategory @TipCategoryID int , @TipCategoryName nvarchar(50) AS BEGIN SET NOCOUNT ON; UPDATE dbo.TipCategory SET TipCategoryName = @TipCategoryName WHERE TipCategoryID = @TipCategoryID END GO CREATE PROCEDURE DeleteTipCategory @TipCategoryID int AS BEGIN SET NOCOUNT ON; DELETE FROM dbo.TipCategory WHERE TipCategoryID = @TipCategoryID END GO
The above stored procedures are as simple as possible; note that the InsertTipCategory stored procedure selects the identity value that was assigned to the newly inserted TipCategory row.
To add our insert, update and delete stored procedures to our EF model, right click on the entity, then select Update Model from Database as shown below:
The Choose Your Database Objects dialog will be displayed; select the stored procedures to add to the EF model as shown below:
There is one last step and that is to map our stored procedures to the Insert, Update and Delete functions for the entity. Right click on the entity, then select Stored Procedure Mapping as shown below:
You will now see the Mapping Details - Tip Category allowing you to map specific stored procedures for Insert, Update and Delete as shown below:
Click on the Select [Operation] Function and fill in the Mapping Details as shown below:
Remember that the InsertTipCategory stored procedure returned a result set containing the identity value assigned to TipCategoryID; you need to fill in the column name under the Result Column Bindings as shown above and map it to the TipCategoryID property.
Finally we need some sample code to insert and update a TipCategory:
using (MSSqlTipsEntities ctx = new MSSqlTipsEntities()) { // add a new tip category TipCategory newTipCategory = TipCategory.CreateTipCategory(0, "Cluster"); ctx.AddToTipCategories(newTipCategory); ctx.SaveChanges(); // correct the tip category name newTipCategory.TipCategoryName = "Clustering"; ctx.SaveChanges(); // query the tip categories var result = from c in ctx.TipCategories select c; foreach (TipCategory c in result) { Console.WriteLine("{0} {1}", c.TipCategoryID, c.TipCategoryName); } }
To verify that the stored procedures are being called, you can run the SQL Server Profiler. Select the Tuning Template and you will see output like the following (you can see the InsertTipCategory and the UpdateTipCategory stored procedures being called in the RPC:Completed event):
Next Steps
- The Entity Framework can utilize existing stored procedures for insert, update and delete operations on an entity. It just requires a little bit of configuration to set it up.
- You can download a backup of the SQL Server 2008 sample database and the Visual Studio 2008 sample project here and use it as a basis for your own testing and experimentation.
- In a future tip we will look at how to leverage stored procedures that return a collection of entities.
- Check out these related tips:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips