How to invoke a stored procedure in Web API using Entity Framework

In this tutorial, we will go over sample code to retrieve data with Web API and Entity Framework (invoking a stored procedure to return data).

 Before getting started, I installed Entity Framework 4.3.0. To learn more about installation, click here....

 For this example, I had installed Northwind database and created a sample stored procedure that would return few records, which we will fetch through Web API methods in this illustration...

Create a sample procedure as below...

   To start with, create a folder called Data Model. Right click on Data Model and select add new item...

In the above, I created a new ADO.NET Entity Data Model named DBEntity. I will be using this one for the tutorial.

 I prefer to create an empty data model and add stored procedures as I go. (this works well with just stored procedures approach)...

Once you click finish, this should open up DBEntity.edmx file. Right click on Stored Procedures and click "update model from database"

Once you do that, make sure it has the connection to the DB server it needs to, if not click on New Connection and enter the server details and make sure the connection is working fine by clicking on Test Connection.

Finally we should see a window where it shows up with three tabs, Add, Refresh and Delete... In the Add tab, expand stored procedures and you should see the one created in first step. Check on the TestProc stored procedure and click finish... (refer below..)

Now, our DBEntity.edmx should be like the one below, with new stored procedure added to it...  To include a stored procedure, right click on Function Import  and select Add function import to add stored procedure.

In the window that appears,  select TestProc from stored procedure name and give "TestProc" for Function import name..

Once you click on get column information, this should give the set of columns that will be returned. Now click on the button "Create New Complex type" and this should create a new result-set as "TestProc_Result". Finally the screen should look like the below image.....  If so, then click ok...

Now, its time to get started on invoking the stored procedure and testing the results....  You can find the entity name in web.config and use it for data retrieval... (here the name is DBEntityContainer)

In my WebApiTestController, will add the following code (class and implementation to retrieve data).

 public class CategoryList
        {
            public int CategoryID { get; set; }
            public string CategoryName { get; set; }
            public string CategoryDescription { get; set; }
        }

        public IList<CategoryList> GetSampleData()
        {

            List<CategoryList> catList = new  List<CategoryList>();

            using(WebAPI.DataModel.DBEntityContainer test = new DataModel.DBEntityContainer())
            {
                var result = test.TestProc().ToList();

                foreach (var item in result.ToList())
                {
                    catList.Add(new CategoryList { CategoryID = item.CategoryID, CategoryName = item.CategoryName, CategoryDescription = item.Description});
                }
                return catList;
            }

        }


Now press F5 and you should be able to see the output as below...

Happy coding...