Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ


Choose your language

InfoQ Homepage News Stored Procedures and Entity Framework

Stored Procedures and Entity Framework

This item in japanese

Lire ce contenu en français

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