Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2010-03-26   |   Comments (3)   |   Related Tips: More > 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:

leveraging stored procedures for insert, update and delete is actually a built-in capability of EF.

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:

note that the InsertTipCategory stored procedure selects the identity value that was assigned to the newly inserted TipCategory row

The Choose Your Database Objects dialog will be displayed; select the stored procedures to add to the EF model as shown below:

Choose Your Database Objects dialog

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:

map our stored procedures to the Insert, Update and Delete functions for the entity

You will now see the Mapping Details - Tip Category allowing you to map specific stored procedures for Insert, Update and Delete as shown below:

the Mapping Details - Tip Category

Click on the Select [Operation] Function and fill in the Mapping Details as shown below:

Click on the Select [Operation] Function and fill in the Mapping Details

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

Select the Tuning Template
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:


Last Updated: 2010-03-26


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, December 12, 2012 - 10:07:01 AM - Jeremy Kadlec Back To Top

Diane,

Can you please post your question here - http://sqlserverquestions.mssqltips.com/questionlist.asp.

Thank you,
Jeremy Kadlec
Co-Community Leader


Wednesday, December 12, 2012 - 4:55:57 AM - Diane Swanson Back To Top

My biggest problem is I keep trying to download Mysql and SQL Server Management Studio to load into my computer and I can't seem to make either one to work. I have unloaded and reloaded both at least 4 times. I need these to work for a class I am taking. Any suggestions?

                                                                                                                                                         Diane


Tuesday, February 15, 2011 - 2:13:21 PM - Mbourgon Back To Top

Freaking awesome.  MANY thanks for this, Ray!  I just found it via the daily email, and this is exactly what we need - a simple-to-build frontend for SQL Server tables.  Read the other one yesterday and got 90% there - and lamented the fact it couldn't use SPs.  Voila!

 


Learn more about SQL Server tools