How to Use SQL Server DML Stored Procedures with the Entity Framework

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:
  • SQL Server Stored Procedure Tips
  • SQL Server Stored Procedure Tutorial

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips