I’ve had the discussion many times about whether or not to use Stored Procedures when consuming my LINQ to SQL or Entity Framework models. I think that Lenni has come up with an excellent compromise in his Rethinking the Dynamic SQL vs. Stored Procedure Debate with LINQ blog post.
Basically, he thinks we use LINQ for SELECT operations, harnessing Dynamic SQL, and continue to use Stored Procedures for the INSERT, UPDATE and DELETE operations.
Lenni is a SQL Server guru who basically wrote the book on Programming SQL Server. His deep analysis of the debate is spot on. Although, I still strive to use full capabilities of the ORM; I can see the need for maintaining control of the data manipulation using Stored Procedures. Also, since you’re often writing way less than half as much data as you’re reading, you shouldn’t see a any real performance decrease for not utilizing a partial UPDATE statement. I hope this will encourage the strict DBAs to allow more dynamic SQL into the DB.