Entity Framework : Part 9 Use Stored Procedures with Entity Framework

Background

This is the ninth part of the Entity Framework 4 learning series. In this post I’ll be demonstrating how to use Stored Procedure with Entity Framework. If we are working on a green field project, it becomes easier to implement concepts like model first development. Using this approach we can define our entities first and based on the entity model we can arrive at the database model required for persisting the data.

In many cases we already have a set of database objects in place. We might have spent lot of time writing complex business logic in database objects such as Stored Procedures and Views. From a return on investment (ROI) point of view it might make sense to reuse them to reduce the time required to convert the complex logic into an object oriented code.

Entity Framework 4 supports stored procedures and views as you might have already seen if you have been following the complete learning series. This is presented as an option in the wizard which prompts us to select the database objects which we want to convert into entities during the generation of entity model.

Pre Requisites

I’ll be reusing the Northwind sample database we have been using all this while for this series. If you don’t have a copy of the database I would recommend you follow the steps from first part of this series to set up the database.

How to use Stored Procedures with Entity Framework 4

If we want to use a stored procedure, we need to follow the steps similar to all other earlier posts until we reach the step in the wizard where we are asked to select the database objects as shown below

I have selected the 3 stored procedures

1. Sales By Year
2. SalesByCategory
3. Ten Most Expensive Products

After finishing the wizard, you might notice that the designer file is empty. Unlike on previous occasions where we would get to see the list of entities for the selected tables we do not get anything for the selected stored procedures. We need to access the model explorer window which displays the stored procedures as shown in the screenshot

If the model browser is not visible along side solution explorer we can navigate to it by selecting View –> Other Windows –> Entity Data Model Browser. In order to use the stored procedure in Entity Framework 4, we need to use a feature called Function Import. What this does is it tries to map the stored procedure to a function. We can invoke the stored procedure as if we would be invoking a function. So lets look at the simplest stored procedure which is Top_Ten_Most_Expensive_Products.Select this stored procedure from the model browser and right click which will display the context menu. Select Add Function Import option which shows a popup with various options

This popup has lots of options like name of the function, return values which can be a list, a scalar value, a complex type or an entity which is already present in our entity model. If I am not sure what columns are returned by the stored procedure , I can click on the Get Column Information button to query the metadata about the stored procedure itself.

For the purpose of this demo I have created a temporary class which can be done by selecting the returns Complex option and clicking on the Create New Complex Type button towards the bottom. This would add one item each to the Function Imports folder and Complex Types folder under the model browser.

Now I can query the database using these functions with the help of an object context like

            NORTHWNDEntities context = new NORTHWNDEntities();

 

            var topTenMostExpensiveProducts = context.TenMostExpensiveProducts();

 

            foreach (var expensiveProduct in topTenMostExpensiveProducts)

            {

                Console.WriteLine(expensiveProduct.UnitPrice);

            }

I started off as usual with an instance of NorthWNDEntities class. I made a call to the TenMostExpensiveProducts function and stored the results in an variable. I loop over the results to display the UnitPrice of the top ten most expensive products.

If you run the project now you should be bale to see the ten most expensive products from the database. This was a simple stored procedure which did not take any input parameters.

Lets look at the other stored procedure which requires input parameters. We’ll use the Sales_By_Year which requires Begining_date and Ending_date parameters to be passed as inputs. We’ll import this stored procedure into a function as

I’ll again store the resultset into a complex type. In order to execute this stored procedure I’ll follow

            var salesByYear = context.Sales_by_Year(DateTime.Now.AddYears(-20), DateTime.Today);

 

            foreach (var salesByYearResult in salesByYear)

            {

                Console.WriteLine("{0} - {1}", salesByYearResult.Year, salesByYearResult.Subtotal);

            }

If you execute the program now, you’ll be able to see the year and the sub total for those years in the results.

Conclusion

As we saw from the code above, its very easy to invoke a stored procedure using Entity Framework 4. We can call all types of stored procedures and handle different types of results like lists, scalar values etc. Here I have demonstrated using the results directly, but we can use options like filtering the results what the stored procedure returns. I’ll leave it to the viewers to try those filter options to get better understanding of the framework.

For most part of my career I have had the privilege of working on green field applications. I personally follow the TDD approach more. Since it becomes very difficult to test the logic residing in database objects like stored procedures, triggers and views I tend to stay away from them. Also debugging stored procedures is difficult. Another disadvantage of using stored procedures could be that the application logic is spread across different layers apart from business logic layer or service layer. If we are using any ORM tools like Entity Framework 4 or NHibernate I would try and stay away from stored procedures as much as possible.

As always I have uploaded the compete working solution to dropbox which can be downloaded as Part9_StoredProcedures.zip

Until next time Happy Programming :)

Further Reading

Here are some books I recommend related to the topics in this post.

Share: