How to use Stored Procedures in Entity Framework

Standard

In the current project we are using Entity Framework for database operations. Entity Framework comes with Visual Studio SP1, which helps you to map tables / views / procedures as entities in C# / VB Code. You can find more details about EF from here : http://msdn.microsoft.com/en-us/library/bb399572.aspx. In this post I am explaining how to use Stored Procedures in Entity Framework.

  1. Add the Stored Procedure to the Entity Model Designer using Update Model From Database Option.
  2. Add Procedure

    Add Procedure

  3. If you are added successfully, you can get the procedure in Model Browser.
  4. Model Browser

    Model Browser

  5. Right click on the Procedure name and select Create Function Import.
  6. Create Function Import

    Create Function Import

  7. It will popups a Windows with Stored Procedure Name, Function Import Name and Return Type. If the procedure returns nothing, you can choose none. If the procedure is returns single value, like UserId, Number Of Rows etc, then you can choose scalar option, where you need to specify the return type. And if the procedure is returns Table or Number of Rows, you need to choose the last option Entities, which will allow to select entities created in the Model as the Output. Sometimes we need to create a View in the DB and need to import it in the Model, so that we can use the View as the return type entity. Select the appropriate return type and click Ok. You can use this in code. In this code I am using a View to return the selected users.
Add Function Import dialog

Add Function Import dialog


using (SampleEntities context = new SampleEntities())
{
/*
* Thanks to Barry Soetoro.
* I was not calling the GetAllUsers function.
List<Users> Users = null;
Users = (from user in context.Users
             select user).ToList();
this.dataGridView1.DataSource = Users;
*/
//Updated Version.
IEnumerable<UsersView> userview = context.GetAllUsers();
this.dataGridView1.DataSource = userview;
}

This will display list of Users in the DataGridView. Happy Coding :)


  • http://FireCongress.org Barry Soetoro

    This certainly does not call GetAllUsers function. All you did was show how to get a list of Users from the Users Entity in the context. Why not show how to call the function?

    • http://www.dotnetthoughts.net/ Anuraj P

      Thank you Barry Soetoro. Sorry I terribly missed it. I will update the post soon with the changes.

  • http://www.dotnetthoughts.net/ Anuraj P

    @Barry Soetoro Thank you. I am updated the code.

  • http://bhupalsetti.blogspot.com Bhupal

    Hi Anuraj,
    Can you please suggest me one function import to return none type like insert update delete statements in the stored procedure
    Here u used the entity type as result i hope
    I selected the none return type but the function not generated in the in context
    Can u please look into this.

    • https://profiles.google.com/anuraj.p Anuraj P

      I don’t think it is possible to create function with void return type in SQL. I am not expert on this I will confirm it and let you know.

  • Pingback: How to use Stored procedure in Entity Framework Code First « dotnet thoughts()

  • Ofer Gal

    Is the type UsersView a class containing the fields expected as results?

    • http://www.dotnetthoughts.net Anuraj P

      Yes, it is.

  • Ofer Gal

    I do the same in VS2010 and the look is a bit different.
    Also the context intelisense does not show the function I imported.
    Any Idea?

    • http://www.dotnetthoughts.net Anuraj P

      Let me check and will revert back to you. Some problem with my VS, EF is not getting loaded.

  • http://www.dotnetpools.com Vinay

    Hi this is really nice article . have a look of similer article for insert,update and delete record using sp in mvc3
    http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=50&title=Insert,Update,Delete-Operation-By-Using-Stored-Procedure-In-MVC3