EF and Stored Procedures

Entity Framework interacts nicely with stored procedures. That being the case, how do you decide when to create your code on the database, or in your repository code using Entity Framework?

One of the things we often forget is that Entity Framework attaches to many different storage methods. You can even attache Entity Framework to files stored in the file system. That being said, any code you implement may not be storage engine specific, and transportable from one storage engine to another. Please note that I said that it MAY work with different engines. All that will depend on how well the EF and Ado.Net implementation fulfills the necessary interfaces for interacting with the store.

Ok, so the first question you want to ask yourself is, what is going to be my target storage engine? The second question is, am I ever going to change my target? In 30 years I have never worked on a project using more than one storage engine, nor converted an application from one engine to another. That doesn’t mean it doesn’t happen. Some people have it happen to them a lot. It depends on the company, the product, and the industry. I still remember reading Joe Celko saying, “Use ANSI SQL Syntax, and your code will be more transportable.”

If your not worried about transportability, and you’re using an SQL Engine, you can consider putting some of the load on the database server through the use of Stored Procedures. In this case, the question changes to something like, “Where am I going to get the best performance??” Or, “Will putting my code in the Repository make my application more powerful?” There are a lot of things you can do in Entity Framework that are super complicated in stored procedures, like creating a dynamic where clause, or dynamic ordering of results. Conversely, there are many things that are often easier in SQL code than in Entity Framework.

Recently I had a problem to solve that in Entity Framework was becoming a twisted, convoluted mess. I was able to write the same logic in a stored procedure with an inline view and one SQL statement. It was easy to understand, more performant because it could address multiple records locally without having to return but a few records, and optimized for the problem at hand.

What I found that was really nice was that by using Entity Framework, I was able to create a POCO (Plain Old CLR Object), call my stored procedure, and Entity Framework converted the data table returned from the stored procedure into a collection of my POCO object. All I had to do to make the magic work was create a POCO with the same properties as the columns in the Data Table (Result Set from the stored procedure). The POCO had to match the column order and data type and property/column names. Making the call to the stored procedure through Entity Framework automatically converted the data without any extra code.

Previously, using Ado.Net I would have to write about 20 lines of code compared to what Entity Framework can do in one one. Tomorrow I’ll provide a specific example using both techniques for you, so it will make more sense.

So, if you’re liking stored procedures, Entity Framework does not exclude that capability. Your choice will depend on what kinds of capabilities your software demands, and what layer best implements those requirements.



  • Ben

    I apologize…the code demonstrated in this editorial did not publish. We’ll get it fixed.

    • John Shadows

      Stuff happens…
      Looking forward to this code.