LINQ to SQL and stored procedures, my quest continues
Recently I posted some reservations on LINQ and in particular LINQ to SQL and the usage of stored procedures. After numerous comments from respected community members I have furthered my quest on the subject and found a very interesting blog entry by Mark Seemann where he shares his thoughts on how LINQ to SQL will impact database development best practices. I feel that he shares my thoughts on the subject and he raises some interesting comments.
I am a firm believer in the current database development best practice of providing a stored procedure interface to a database to provide a layer of abstraction, increase maintainability and allow for performance improvement through database query optimisation and tweaking. Sometimes this includes blurring the ideals of having the business logic implemented completely outside of the database in the hypothetical business logic layer or object workspace.
If the business logic is to truely exist in the business logic layer then LINQ to SQL might really be the future and maybe some of the database development best practices should be tossed out of the window in favour of a strict database schema with all business logic constraints enforced by the application developer. From my experience this just doesn't work in the long run. Sure an application developer can use an ORM tool already to do a lot of what LINQ to SQL does and what do you get, a very loose database schema that leaves a lot to be desired from a data integrity and performance perspective. Why should a database query be designed by the application developer doing the business logic layer? Surely a database guru should be featured somewhere in the process. At least with stored procedures the application developer could define the interface and the database guru could optimise it thereafter.
So maybe a hybrid approach is the way forward. Yes LINQ to SQL does provide support for calling stored procedures and I think that it has its benefits, but from my reading so far I just don't see myself using delayed insert or update stored procedure execution to update business entities involving complex business inter-dependencies.
So the questions that remain to be answered, will a pure LINQ to SQL solution have an impact on performance? Will it suffer in the long run?
Another interesting question that comes to mind is that of SQL Server Reporting Services (SSRS). If the idea is to push the business logic into code then I suppose the only feasible way of exposing it would be through Service-Oriented Architecture (SOA). So are we going to see extensions to SSRS to talk to web services and Windows Communication Foundation (WCF) instead of stored procedures and T-SQL statements?
Update: I have subsquently found that SSRS has the ability to call web services using its XML data extension but it looks really tedious.