Stored Procedures and Entity Framework
- Share
-
- |
Read later
Reading List
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
Submit
Submit
Related Sponsor
Deploy and manage industry leading databases in minutes. Our platform comes with high availability, auto-scaling, auto-backup and more. Free to try.
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
Community comments
More NIHS
by
Mark N
Posted
Re: More NIHS
by
Mark N
Posted
Re: More NIHS
by
Jonathan Allen
Posted
Re: More NIHS
by
Jonathan Allen
Posted
Re: More NIHS
by
Roopesh Shenoy
Posted
Re: More NIHS
by
David W
Posted
Re: More NIHS
by
Mike Lythgoe
Posted
Idealised representation?
by
Stephen Anderson
Posted
From my experience...
by
Russell East
Posted
More NIHS
by
Mark N
You'd think that the MS EF developers would have looked at the existing ORM frameworks and usage before starting out...
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.
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
Also if you are doing this - "deploy the numerous applications that depend on the database" - you are doing something wrong (or legacy).
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.
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
I didn't say that you can skip testing, I just said you don't have to redeploy. For shops running a lot of native clients this can be a huge deal.
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.
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
Relational databases may not be sexy, but they still form the core of most enterprise systems. And some of those databases are a decade or more older than Java.
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.
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
RDBMS solve a lot of problems really really well - and when working with RDBMS, Stored procedures/functions are really important when you want to manipulate a lot of data and want to squeeze performance - not needed for 95% of the use cases (where other things like maintanability will take higher priority), but when they are needed, they are almost irreplaceable.
As always this is a good feature if not abused.
As always this is a good feature if not abused.
Re: More NIHS
by
David W
Oh my god, RDBMS's are so like 10 years ago. Like, we just use LINQ with our EF Pocos in our abstract repositories in 2012. I mean our data store isn't even like relevant, ands it's so totally rad cause our mocked unit test framework says so. Ya just wait til someone actually starts using your database and your dealing with million row tables, auditing, row level security, transactional processing, and denormalization issues. You may wish you had put a little of that logic at the database level instead of relying on your junior programmer to not code that repository class that returns the entire Products table to display the fist 20.
Idealised representation?
by
Stephen Anderson
Relational theorists would say that a relational schema *is* an "idealized representation of the data" ;)
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.
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
I used to be pro ORM for years (mainly NH). Until i had to work on a very large system, which had to deal with tens of thousands of web requests per minute and some. The typical layered architecture, just didn't scale.
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.
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.
