How to Call Stored Procedure from Entity Framework in ASP.NET MVC

You can call Stored Procedure and Views using Entity Framework, I recently had to use one and didn’t find any good article describing this, so I am writing this one down.

Below is a Stored Procedure I have prepared for this example. As you can see in this stored procedure, there is 1 input parameter and 1 output parameter. I have tried to keep the stored proc as simple as possible :)

CREATE PROCEDURE SP_TEST_EF
@username varchar(50),
@total INT OUTPUT
AS
BEGIN
SELECT * FROM dbo.Users WHERE username = @username
SET @total = (SELECT COUNT(*) FROM dbo.Users)
END
GO

Now that we have defined our stored proc, let move towards our next step.

Open your .edmx file and right click on the designer and select the “Update Model From Database”

then select the new stored procedure added from the list and select “Finish”.

Now right click on the designer again, and select the “Add -> Function Import” option this time.

Select the stored procedure from the list of Stored Procedures, give a nice function name I have given mine “Test_EF” now as I have my stored proc return a value I have used Complex Type. To create a complex type simply click on the “Create a New Complex Type” and Visual Studio will create one for you. Click ok.

And so if all is ok, you should be able to see the function added to your model browser as shown in the screenshot below.

And now you can use the stored procedure like any other table call,

For example:

using(var context = new SimplePmEntities())
{
    var data = context.Test_EF("yasser", 0);
}

Hope you found this useful ! Cheers !

About Yasser Shaikh

Hello, I am a Web Dev, employed with Media.Net where I primarily work on ASP.NET MVC and Angular JS based developement.

Questions? Enquiries? Comments? Or even just want to say “Hi” to me, you are welcome to all of that. Just drop me a message at “mail@yassershaikh.com” and I’ll get back to you as soon as possible.

Cheers !

2 comments

  1. I read your article and will like to add this comment . When Using a stored procedure to return numerous rows, you have to be very careful because when the procedure is invoke all results are retrieved. This means that even if you apply a take lambda expression to control record returned, it will still go ahead and execute the stored procedure and all record will be returned. You may consider view in such situations.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>