Connect

  • Connect with me

  • Monday, October 11, 2010

    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

    select sp

    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

    model browser

    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

    function import

    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

    import sales by year

    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.

      

    11 comments:

    1. For visual studio 2008 this doesn't work. I can not "call" stored procedures via context.... event it's in function import it's not visible via provider.

      ReplyDelete
    2. @DryThyme I hope you are using EF 4. This is a feature of EF 4 and not VS 2008. I have not tried using EF 4 with VS 2008.

      ReplyDelete
    3. Anonymous12:14 PM

      Articles on EF4 were really good for beginner like me.Could you please post article on T4 Templates and POCO?

      ReplyDelete
    4. Anonymous4:02 PM

      Very good lessons and examples. Great for someone to catch up quickly on Entity Framework. Thanks again.

      ReplyDelete
    5. Anonymous2:00 PM

      Hi Nilesh:
      I tried to call Stord proc via context but can't.
      I imported stored proc as Function, can see the proc but can't call.
      what could be the reason?

      ReplyDelete
    6. Anonymous4:13 AM

      NORTHWNDEntities context = new NORTHWNDEntities();

      This is the error on my project.


      where did you get these.
      Error 1 The type or namespace name 'testEntity' could not be found (are you missing a using directive or an assembly reference?)

      ReplyDelete
    7. Wonderful job man. Keep posting like this in future.

      ReplyDelete
    8. Anonymous4:09 PM

      Hi Nilesh,

      good articles.

      I hope you have the right solution for my problem.

      I want to read “image” from Northwind.Employees and to display in a PictureBox using Entity Framework
      OR
      I want to read a “varbinary” field(a photo) from a table using Entity Framework .
      It’s no problem to read, insert and to display the image in PictureBox using ADO.NET.

      Thanks for your help
      Husso

      ReplyDelete
    9. Hi Husso,
      I haven't tried working with Images or varbinary data types prior to this. From my recent readings I understand that Image data type in SQL Server is deprecated and it is advisable to use varbinary. We need to remember that varbinary has a maximum capacity of 2 GB.
      Just a suggestion from my side. Instead of binding image data directly ffrom the DB to a UI control like picture box it would be better to separate them into different layers to decouple the business logic from persistence. If you are doing this as part of some proof of concept or for your own learning then its fine.
      Here are few links which might be useful to you
      http://stackoverflow.com/questions/5279481/entity-framework-4-image-type
      http://blogs.microsoft.co.il/blogs/bursteg/archive/2008/05/12/ado-net-entity-framework-support-filestream.aspx

      Hope this helps

      ReplyDelete