I'm belatedly thinking about LINQ, and LINQ to SQL.  Stephen Walther urges "Death to SQL!" in his ASP.NET 3.5 book.  Mike James of VSJ doesn't like databases either.  Personally, I've never been as happy working in SQL compared to objects and recipes, but I thought there were good reasons for using Stored Procedures - which LINQ to SQL seems to blow out of the water.

Efficiency was my first concern.  However everything that I've seen says that LINQ to SQL is sent to the db as an efficient request, ie the clever bits are done in the db, not in the application.

What about SQL Server query plans?  Stored Procedures provide a security blanket - surely LINQ to SQL will mean that tables will have to be more open to modification, rather than just to SPs?  SPs can get several things done, while LINQ to SQL boils down to just one statement at a time doesn't it?  Providing a SP interface provides a good discipline (a database layer) - I think there's got to be a higher chance of some LINQ to SQL programmer not doing things correctly, eg forgetting to put something in a transaction.

Why haven't the database aficionados been up in arms?  Has anyone done some analysis of how SPs compare with LINQ to SQL from a security and performance point of view?

Chris

PS  I'm not utterly convinced that LINQ to SQL is easier to understand than SQL (unless it's hidden from you by the framework).  You still have to know how to frame your queries so that they run in the best way.

I've never understood how LINQ could be death to SQL.  LINQ allows you to do stored procedures just as easily as raw SQL..  The main concern with LINQ that I hear is the inadequacies of the current version that make life just a bit difficult. I like the idea of having the abstraction from a particular database dialect of SQL, so that you can use a variety of actual data sources, but I've always found this is a pretty marginal requirement commercially. You don't switch much between databases, and if you need to regularly access such things as XML sources for data feeds etc, you'd end up using more efficient ways.

This argument about Linq to SQL ignores the differences in scale that you'll find in commercial applications. Normally, in an enterprise-scale application, you don't get the option: you use stored procedures, functions and views. You are never given the choice of having access to the base tables. No auditor would allow it, even if you managed to drug the DBAs into aquiescence. If, on the other hand, it is a small database in a small or medium enterprise, then you can let the developers play with techniques that perform poorly and are insecure or use silly things like nhibernate. (I approve of Entity Framework because it allows you to do it properly and professionally if you want to)

The task of fixing any database where developers have been using dynamic SQL to access base tables is huge and time-consuming. It can be done but it is such a horrible task that no experienced DBA will agree to it. Re-Engineering the schema becomes almost impossible. Added to that is the task of securing the tables. Almost impossible.