Using Stored Procedures in the Entity Framework with Scalar Return Values

Although stored procedures may be on their way out (see post here: http://stackoverflow.com/questions/216569/are-the-days-of-the-stored-procedure-numbered), they are still supported in Entity Framework and can be used relatively easily. There are many tutorials on how to use a stored procedure in the Entity Framework to return a set of data and bind it to an Entity. But I had a little more trouble figuring out how to call a stored procedure that returns a scalar value. This tutorial shows the basics for calling a stored procedure using the Entity Framework.

You can download the sample code using the link at the bottom of this tutorial.

First, run the "generate_schema.sql" script in the EntityFrameworkStoredProcedure.Data project to create your database schema for the sample and generate a stored procedure. I have a really simple stored procedure that just returns the primary key of the first row in the sample Customer table.

If you are following along and want to get this set up, here are the steps to get your own project set up using a stored procedure in the Entity Framework. Create your new Entity Framework (.edmx) file in Solution Explorer. The sample stored procedure we are using is called "sp_GetFirstCustomerId". Now, when you update the database model, you are presented with a dialog to choose the database items to add to your model.

Once you click Finish, the model will be read and the dialog will close. Now, you might think, what the heck? Nothing seems to happen. But basically here you have just told Entity Framework about your stored procedure. But to actually get it hooked up to the context so you can call it, you need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.

In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model, choose your procedure from the drop down list, and choose the return value of the procedure to be Scalar in this case.

Now you can call this method using code like this:

// create new instance of the context
Data.devtoolshedEntities context = new Data.devtoolshedEntities();
// call the stored procedure to get the return value
System.Nullable<int> iReturnValue = context.GetFirstCustomerId().SingleOrDefault();
if (iReturnValue.HasValue)
{
    // return was successful!
}

Now, if you run this as is, it all should work and get your return value fine BUT if you look closely, you will see that I have made a key change to my stored procedure. Originally, I used the keyword "return" in the stored procedure to return the value from the function but after some pain and suffering, it turns out that Entity Framework cannot support Stored Procedure Return scalar values out of the box. BUT there is a work around. For instance, here is how I originally declared my stored procedure. Notice the "Return" keyword at the bottom.

To get this to work with Entity Framework, you need to use "Select" instead of "Return" to return back the value. This will return 0 or 1 nullable rows as a side effect so make sure to check for that but it will also make it so that Entity Framework can actually call your procedure without an error. Here is the updated stored procedure declaration using "select".

You can download the code for this project here.

EntityFrameworkStoredProcedure.zip