Stored Procedures and Entity Framework
- Share
-
- |
Read later
Reading List

A note to our readers: You asked so we have developed a set of features that allow you to reduce the noise: you can get email and web notifications for topics you are interested in. Learn more about our new features.
Much has been written on the topic of ORMs and its failings. Most of the objections fall into two categories: Separation of Concerns and Object Oriented Design. For the Entity Framework we have good news for one these.
Separation of Concerns
Stored procedures are not just a way to cram a ridiculous amount of business logic into the database; it is also a way to keep a ridiculous amount of storage logic from being crammed into the application layer. It allows the application to see an idealized representation of the data without revealing the machinations of the DBA. The assortment of staging tables, denormalized reporting tables, views, and table functions are all hidden behind simple procedure calls that form the database’s public API. With care, everything from minor performance tuning to full scale refactoring can be done without the need to redeploy the numerous applications that depend on the database.
Over the next two versions, Entity Framework intends to make using stored procedures much easier. In version 5, which is nearing release, we see the much needed Table-valued functions and the ability to batch import of stored procedures into a model.
Table-valued functions are an especially good match for ORMs. TVFs are far more flexible than normal stored procedures or views, but without dynamic SQL generation one cannot full advantage of them. And really, SQL generation is the key feature that separates an ORM from a glorified data mapper.
Unfortunately this is only available for developer using the modeling tools. If you are using Entity Framework’s Code First technology you have to wait until Entity Framework 6 to get any kind of stored procedure support, let alone TVFs.
Object Oriented Design
The topic of OOP Design is a hard one. By their very nature ORMs want simplistic DTO-style objects with default constructors and public properties upon which the ORM can layer lazy loading, change tracking, and the like. But developers who favor Object Oriented Design tend to prefer rich objects with complex constructors and a limited public interface. Columns such as CreatedBy or CreatedDate should be represented by read-only fields and matching properties so there is no chance of accidentally changing their value.
Unfortunately we aren’t going to see this any time soon. You can do some things with private setters, but in general entities are still going to look more like DTOs than true business objects. Long term, Custom Code First conventions may help. This promising feature was supposed to be part of EF 4.1, but was cut due to the complexity of the design and the general feeling that it just wasn’t ready. Sergey Barskiy has an article demonstrating what this was supposed to do.
Rate this Article
- Editor Review
- Chief Editor Action
Hello stranger!
You need to Register an InfoQ account or Login or login to post comments. But there's so much more behind being registered.Get the most out of the InfoQ experience.
Tell us what you think
More NIHS
by
Mark N
Besides EF being EJB 1/2:
<quote>By their very nature ORMs want simplistic DTO-style objects </quote> Sigh. This is NOT true.
As for SP's, if this article had not mentioned ORM, I would have thought it was from the 90's. The world is no longer RDBMS-centric. This sort of mentality ignores things like caching and "the cloud". If you want to massively increase your development and maintenance of an application and gain very little value, use SPs and views.
Re: More NIHS
by
Mark N
Additionally, this is a fallacy. Making changes a the db does not relieve you of testing all the applications nor coordination of applications. It also increases duplicate code as each "app" must recreate logic that cannot be done at the data layer and also logic that must be duplicated at the data layer because of the procedure type languages.
Re: More NIHS
by
Jonathan Allen
As for the overall theme, well if you don't believe that stored procedures are important then these features are not for you. Pretend they don't exist, they won't get in your way.
Re: More NIHS
by
Jonathan Allen
As for ORMs, there are still many, many people who see them as just another way to inline SQL, a practice that was popular in the 80's and discredited in the 90's.
Features like this are an important compromise between the extremes on both sides.
Re: More NIHS
by
Roopesh Shenoy
As always this is a good feature if not abused.
Re: More NIHS
by
David W
Idealised representation?
by
Stephen Anderson
To a degree, I'd even agree. C.J. Date and others are absolutely correct that the perceived flaws in the relational model are actually flaws in the products that implement it, and that there is no such thing as the O-R mismatch as popularly understood - object instances are properly stored as complex column values, not rows. Of course, the industry support for this is abysmal.
From my experience...
by
Russell East
In reflection ORM simplified some things, but made other normal issues more complicated. For instance, i don't have to write sql (so what), but i had to worry about lasy loading, Select n+1 and reshaping data from my domain to get the data i needed for my screens. Its only dataaccess!
I have chosen an architecture based around CQRS and event sourcing (another story) and although its a mind shift, its made my work and the system easier. I am not using a rational data model as it just doesn't fit. My database reads are from denormalised tables and my stored procs are fairly simply and i have fewer layers to get data out of the system.
My domain now is more OO than the typical entity model, I get encapulation without having to worry about mapping properties to fields or another mapping concerns. I know entity models can have a bit of logic added to them, but i doesn't make a rich domain model.
I feel that using sprocs again, maybe considered old school, but it works and out performs orms. I don't place logic into the sprocs, but i do feel that i have better SoC and less coupling between my domain and database. I now wouldn't go back to an ORM. a typical trait from developers that are pro ORM, is that they under value the database. you can get away with this kind of thinking when working on smaller projects.