One Size Fits All (not)
I recently re-read SQL Server 2005 Bible, a great book that covers everything you ever wanted to know about SQL Server but were afraid to ask. At one point in the book the author says that your data abstraction layer should be in your database in the form of stored procedures and he gives some good arguments as to why.
Right after that I read Pro LINQ Object Relational Mapping in C# 2008, another excellent book that shows you how to create an ORM using LINQ (both LINQ to SQL and Entity Framework) . When introducing the fact that you can use stored procedures in LINQ, the author very definitely says that even though they are supported, you shouldn’t use them and he gives some good arguments as to why.
Two diametrically opposing views in two really good books. The error both authors make is thinking that one size fits all. The truth is that “all” can vary widely so one size just ain’t gonna cut it.
My view is that everything we have in our industry is a tool and each tool has a range of uses.
Example: I recently joined a project that heavily uses stored procedures in line with the “SQL Server Bible” author. I think it is appropriate given the huge amount of data the application has to deal with. Tuning many queries within an inch of their life, including being able to completely rewrite them or split a single query into several, is very necessary on this project. Using LINQ or some other ORM tool to only generate the SQL and never use stored procedures would just not work.
Another Example: I was working on a project a couple years ago where LINQ to SQL would have been perfect for fast creation of a Data Access Layer in an existing application that needed some major enhancements, but unfortunately we had to still support users with Windows 2000 machines and .NET Framework 3.5 doesn’t support W2K, so we were stuck with using typed datasets. But we didn’t use stored procedures for our abstraction layer. It really wasn’t necessary. Yes, some tuning was needed, but the app usage was mild and the amount of data relatively small, even after many years of use, so an ORM would have been great and would have made our lives simpler.
Anyway, the moral of the story is use the tool that is appropriate and remember that absolutes are unobtainable.