Using a Stored Procedure in Entity Framework 4

In my latest contract, the DBA has opted to use SQL Server stored procedures for all data access. Preventing direct table access may ensure maximum security and performance - but it sure reduces the fun for the front-end guy (me).

I’d been doing my screen prototypes with Entity Framework 1 and getting pretty good at manipulating the mapped objects. Arrrgh.

Just for fun, I decided to test out EF 4 against the first stored procedure I received – Inserting a contact. It’s not difficult but you want to watch for a couple of gotchas.

When you choose the database objects in EF, you can add stored procedures. No surprise about that. What’s strange is that you’re just making the designer aware of the SPs, not actually doing anything with them. (This is unlike tables where the OR mapping is automatic.)

includingsp

For stored procedures, you need to go into the Model Browser, drill down to the Function Imports area, right-click and select Add Function Import.

addfunctionimport

I got messed up when using the Function Import because I didn’t read the dialogue box text carefully enough. My stored procedure adds a contact to the database and returns the ContactID  and a returnCode. So, I selected Scalars and an Int32 type as the return. Oops. The label says “Returns a Collection Of”. After some trial and error, I learned that the correct choice was None.

EditFunctionImport

BTW, the preceding dialogue box would be better if the combo box were at the top so you choose the stored procedure name and then the Function Import Name could default to the name of the SP you just selected.

With the mapping of the stored procedure in place, the ASP.NET 4 code is straightforward to call the InsertContact stored procedure. The ContactID comes back inside the ObjectParameter object as its Value property:

Protected Sub Page_Load(ByVal sender As Object, _
                        ByVal e As System.EventArgs) _
                    Handles Me.Load
    Dim ent As New WebApplication1.DBEntities1
    Dim objparam As New System.Data.Objects.ObjectParameter _
                        ("contactID", DBNull.Value)
    Dim status As Integer = 0
    status = ent.InsertContact(objparam, "Billy Bob", _
                               "Thornton", "Goof", "These are notes")
    Label1.Text = "Status: " & status.ToString() & " , ContactID: " _
                             & objparam.Value.ToString()
End Sub

Maybe tomorrow night the DBA will have progressed far enough so I’ll have an SP that returns a bunch of data to EF 4.

Ken

13 Comments

  • According to Microsoft's own Patterns and Practices recommendations, all database access MUST be via stored procedures in order to be secure, so why it has taken them 4 iterations of EF to make working with stored procedures somewhat easy is bewildering to me. From the above information, Microsoft still has a long ways to go in terms of AUTOMATING the process of integrating stored procedures into EF.

  • One side effect is ef still use its own parAms and sp_executesql under the covers. Sp_exececutesql is disabled in our envirnoment so no ef for us.

  • I've always been a fan of Stored Procedures rather than directly access tables.

    Using SP's allows your DataBase structure to change without having to change code - just the Stored Procedure. (just one advantage).

  • @Fred & @Ryan,

    Admittedly I'm a bit biased, but I think if you will look at the most recent recommendations from the patterns & practices folks (such as in the second edition of the application architecture guide) the story about whether or not to use stored procedures is not nearly so cut and dried as you make it out to be. They have a place, but they are not the only recommended way to do things.

    - Danny

  • @Daniel Simmons,

    True stored procedures are not the only way to do things. There are right and wrong ways to do just about everything. Stored Procedures happen to be the correct way the majority of the time. When regarding security of data, stored procedures are hard to argue with. When we speak of performance they are the right choice the majority of the time except for those little applications dealing with such small amounts of data that stored procedures do not provide much benefit. Additionally, stored procedures mean you typically change code in one spot. From a database professional's perspective that means you change code in SQL and deploy it via SQL. The alternative is to edit embedded SQL in applications. That means you typically need two developers to edit and deploy one piece of code. It is a bad practice that only experience typically teaches people.

  • Firstly - nice article.
    From a security perspective, strongly typed stored procedures offer an extra layer of protection - since they allow fine access grained control and offer protection against sql injection.
    I would certainly7 mandate them.

  • It is really sad that still .NET community is way behind as far as data access layer goes. Please check out how Java community is handling this way better using Hibernate.

  • IMO security is not really a good enough reason alone to use sprocs. Any decent data layer using typed parameters provides the same security against SQL injection. That practice is somewhat old and dated. Unless you have a really good reason for using sprocs in your development pipeline, it mostly just adds an extra layer of complexity on top of your data layer.

  • "When we speak of performance they are the right choice the majority of the time except for those little applications dealing with such small amounts of data that stored procedures do not provide much benefit"

    Oh hey, someone still thinks its 1999!

  • "Oh hey, someone still thinks its 1999!"

    lol

    Funny but harsh.

  • Tuesday, September 07, 2010 9:30 AM by David Taylor

    "When we speak of performance they are the right choice the majority of the time except for those little applications dealing with such small amounts of data that stored procedures do not provide much benefit"

    Oh hey, someone still thinks its 1999!
    ------------------------------------------

    It's a shame it isn't because over a decade later you have "fashionable developers" who haven't got a clue about databases nor want to know either - they just don't get it. Either through laziness or lack of software engineering education or self-discipline - in most cases a combination of all three - they are developing over engineered, over complicated, unreadable, unmaintainable code which even they can't understand and are putting their projects and the business at risk.

    Scary and dangerous.

  • Seriously - Isn't there a time and place for both?!?

    e.g. in the RemoveFromCart method in "the complete ShoppingCart class" at http://www.asp.net/mvc/tutorials/mvc-music-store-part-8. Granted this isn't the worst performance-choice ever made, but it does seem a little silly to pull the "cartItem" record from the db, send it to the web server, decrement one field and then send it back for update. Too many of those on a reasonably loaded site and you'll have a real performance problem...

    Isn't the real question "why doesn't the Entity Framework have more robust support for stored procedures?"

  • There's a certain amount of arrogance you come across in this industry where some people think "my way is the only way to build things properly". The tendency is that assertion is usually not true. Much like the guys who used to say "if you want to build it right you have to build it in C++".

    I've been designing high performance apps (web and desktop) for 15 years and these days I tend to avoid store procedures. Mostly because I've had to do conversions from one database to another or build in support for agnostic database access. Stored procedures tend to be cumbersome in that case. I've also never seen a case where inline SQL required 2 developers to deploy.

    When it comes to today's data binding and generated objects it's often less code to use these object layers to interact with the database for you, especially when you use attributes to simplify validation.

    In the end it's often best to prototype a few different ways to architect things up front and pick the solution that will best meet your needs for security, maintainability and performance. There's no single way to handle it, so find out what's going to work best for the project you're working on.

Comments have been disabled for this content.