Using stored procedure with entity framework

Today I will show you how you can use stored procedure in Entity framework.

Create Stored Procedure

So lets start with Stored procedure creation. open Sql Server management studio and select your desired database which you are planning to use for this exercise. Expand Database node

  • Move to Programmability
  • Stored Procedures
Right Click on Stored Procedures node and select Create a new stored procedure

Stored Procedures in Entity framework

For demonstration purpose i create a simple stored procedure. This stored procedure return Author ID, Author First name and Last name as a Author name on the base of state Code.

Stored procedure Code

       Create PROCEDURE [dbo].[uspGetAuthorsByState]
	-- Add the parameters for the stored procedure here
	@pState varchar(3)=null
AS
BEGIN
	
	select au_id,au_fname+' '+au_lname as AuthorName,zip from authors where state=@pState
END
        

After Stored Procedure creation Hit F5 to execute this. you will see success message in Message window.

Connect Database With you Asp.net Application

Now Open Visual Studio and Create a new Asp.net MVC Template project.
Now Create a new folder in your project name it "DataBase". Right click on newly created folder and select "Add"-> "New item".
From this menu select "Data" From left Menu and Select "ADO.NET Entity Data Model".

Connect with Databse

Now provide your database credentials and press oK. Visual studio will bring up screen to ask to you select Database object
Stored Procedure in Entity framework

in above image i highlight some points which are you should checked when importing stored procedures in your application. After Successful establishing connection you can see your imported stored procedures in model browser.

Stored procedure in model browser

TIP

If you can't see Model browser window in visual studio then click on your .edmx file in your database folder. it will bring up Entity framework designer layout. Now select any Entity and right click on it. At the end of the option list you will see "Show in model browser". Click on it and it will open up model browser window.

Using Stored Procedure in application

Now our recipe is ready for cooking. So add a new controller in your controller folder and following code snippet.

Controller Code

         public ActionResult StoredProcedureWithEntityFrameWork()
        {
            TutorialsDataBaseEntities context = new TutorialsDataBaseEntities();
             var AuthorsList=   context.uspGetAuthorsByState("CA");
             return View(AuthorsList);
        }
                
This controller return a strongly type view which contain same data-type which have our stored procedure. In intellisense you can get same property name which you define in your stored procedure.i also pass a parameter 'CA' which need our stored procedure for query manipulation.
Now add Corresponding view for this controller.

View Code

                  @model IEnumerable<TutorialsProject.DataBase.uspGetAuthorsByState_Result>
@{
    ViewBag.Title = "Stored Procedure With Entity FrameWork";
}
<table>
            <thead>
            <tr>
            <th>
             Author Name
         </th>
            <th>
             Zip
         </th>
     </tr>
 </thead>
            <tbody>
        @foreach (var item in Model)
        {
            <tr>
            <td>
                    @item.AuthorName
                </td>
            <td>
                    @item.zip
                </td>
            </tr>
        }
        
    </tbody>
</table>
        
This view generates a table which show list of authors which city is 'CA'.

Result

Run your project and navigate to corresponding view.