Design: Classic stored procedures or Entities?

With the new entities framework to be released in the next service pack some of our customers and team members are starting to discuss what is the correct pattern. I wanted to extract my position around the choices.

I am big supporter of having options, in this case I don’t consider one better than the other one, the stored procedure option can be the best choice for one scenario and LINQ, with all the different flavours including Entities, for another one.

You can go all the way separating process layers using stored procedures that may sound perfect for your scenario. In this scenario the architects and developers must maintain the complexity inherit from this approach as the data is usually quite tight to the processes. This adds risk to the projects making them more complicated to maintain (you need to involve the DBA for schema changes!). Some people may argue that this is the most performance approach but I am a strong believer that usually performance is not well understood. What is fast enough for an application? Do you need to go to the extreme performance when it may not be needed, sacrifying functionality or simplicity? If that is the case SP approach can be the best for you.

Now on the other side, I love the idea of separating the physical model from the logical model (so I can get rid of the DBAs! J). Business entities are much better represented with the entity framework and gives developers another layer to abstract the schema complexity, using strongly type models that can help detecting errors at early stage. Yes, there is a performance penalty price to pay, but maybe the functional benefits really help the business to develop quality software reducing the project risk. Have you measure how slower is it? You will be surprise with the results if you correctly design the entities.

The LINQ story is quite powerful as well, it is true that you have to consider learning curve and the community is taking longer than expecting to digest it. But with LINQ to entities things are starting to get more sense and I can imagine more people jumping into it.

What I think is cool? ADO.NET Data services (do not confuse with SQL Server Data Services! ), as is a great story for another cool technology like Silverlight. In this model you have your physical database completely abstracted from the model as you use the entity framework, you can use LINQ to query it and then post it using REST. This will send the results using XML, transformed to a struct using Silverlight Data services directly binding the results to your grid! That’s what I call functional abstraction.

To conclude, I still believe that it depends on the scenario, the beauty of this is to have options.