Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

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

Error on trying to set value

When I attempted to access the value using System.Nullable I get the error "Cannot implicitly convert type 'System.Data.Objects.ObjectResult' to 'int?' " When I changed the variable holding the return to System.Data.Objects.ObjectResult I get "Cannot implicitly convert type 'System.Data.Objects.ObjectResult' to 'System.Data.Objects.ObjectResult'"

When I look up that error message in Bing I get no search results returned. What was wrong?

Reply to Error on trying to set value

Use FirstOrDefault() method of Stored procedure entity. Like this db.PRNumberExist_Get(PRNumber).FirstOrDefault()

Reply to Error on trying to set value

Use FirstOrDefault() method of Stored procedure entity. Like this db.PRNumberExist_Get(PRNumber).FirstOrDefault()

doh

so much bad practices in one blog entry...

Don't use "sp_" as a prefix to stored procs

A helpful hint --
Just wanted to let you know that you should avoid using sp_ as a prefix. This is considered a very bad practice because SQL Server assumes that all sp_ stored procs live in Master, so it will always look first in master, regardless of which database your connection string points to.

References:
http://msdn.microsoft.com/en-us/library/dd172115(v=vs.100).aspx

http://msdn.microsoft.com/en-us/library/aa214379(v=sql.80).aspx

Return keyword in SQL

This should not be used to return a value as it is meant to be used for error codes etc. This is probably why it doesn't work as you're attempting to use it for the wrong reasons.

As you suggest SELECT @value works fine and that's how it should be used in the SP.

Thank you!

You helped me very much :)

Thanks!!

Thanks!!

or ...

or you can change sp, and put

SET NOCOUNT ON;

so that the return value is return instead of RowCount

Issue

Hi,
When I set the return value of the stored procedure to 'Scalar', I am unable to call the Function.
However, if I set the return value to 'Entities' I can call Function.

I have followed all above steps. Any ideas? Using .Net 3.5

Thanks

Issue

Hi, i had the same problem,
seems to be fixed by Microsoft in Entity Framework 4.
look at :
http://stackoverflow.com/questions/2248300/entity-framework-stored-proce...

I really loved reading your

I really loved reading your blog. It was very well authored and easy to understand.
web designing vizag

thanks

thanks

Thanks!

Super helpful!
Just what I was looking for!

Thanks

Muy buen post, va directo al punto y funciona perfecto.
Gracias.

Using select instead of return did the trick

Thank You!
This solved my problem
hh

Thank you So much

Your article is much helpfull.

Thanks for you kindly

Thanks for you kindly help.....
iam using RETURN keyword and suffered two days to find out the error. after reading your notes, now i modify with select instead of return now it works.
Thanks parthiban

Thanks!!! :-)

Exactly what I was looking for,
God bless you, thank you so much!

Thanks

Just what I was looking for, too!
Burton Roberts

Thanks!

Thanks For the tuto, very accurate.
Greetings
Jose.

Example usage, please

Hi Ben

I can see how the edmx file is adjusted, but then I'm at a loss as to what to do next.
Can you give a code example of how code in a Silverlight project would make the call and get hold of the returned value.
I'm learning .Net Ria services, and it seems I would have to make an entry in a domainServices file for my Silverlight project to be able to call GetFirstCustomerId().
I can't find any such code in your zipped solution.

Thanks.

James

using stored proc in entity framework

Yes same thing i was looking.Now i have idea how to use.

thx

thx so much...... it helped me a lot..
appriciated ur efforts

Thanks

This article is great . it helped me a lot. Thanks..keeep your good work further. ok?

ramya

good document

Hi

you have written this lines of code,littile bit confusion..! where can i write this code in my apliction.?could you please reply me as soon as possible......

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