My understanding of Linq to Sql is it will take my Linq statement and convert it into an equivalent SQL statement.
So
var products = from p in db.Products
where p.Category.CategoryName == "Beverages"
select p
Just turns into
Select * from Products where CategoryName = 'Beverages'
If that's the case, I don't see how stored procedures are useful anymore.
Sprocs are another tool in the box. You might use your fancy automatically-adjusting wrench for 90% of your tasks, but you can't use that shiny thing on stripped nuts. For that a good ol' monkey wrench is your best friend. Unless you break the bolt, in which case you're stuck with assembly.
if that's all you ever did in sql, you didn't need sprocs before!
Security.
I've seen several "security best practice" guidelines which recommend you do all your data access via SP's, and you only grant privileges to execute those SP's.
If a client simply cannot do select or delete on any database tables, the risk may be lower should that client be hacked.
I've never personally worked on a project which worked this way, it always seemed like a giant pain in the backside.
Ah, the subject of many a debate.
Many would argue these days that technologies such as LINQ-to-SQL generate such good SQL these days that the performance advantages are marginal. Personally, I prefer SQL experts tuning SQL performance, not general coders, so I tend to disagree.
However, my main preference for stored procedures has less to do with performance and more to do with security and configuration management.
Much of my architectural work is on service-oriented solutions and by treating the database as a service, it is significantly aided by the use of stored procedures.
Principally, limiting access to the database through stored procedures creates a well-defined interface, limiting the attack surface area and increasing testability. Allowing applications direct access to the underlying data greatly increases the attack surface area, reducing security, and makes impact analysis extremely difficult.
Stored Procedures and Linq to Sql solve different problems.
Linq to Sql is particular to Microsoft SQL Server.
I tend to prefer using stored procedures for several reasons:
The downside is that it can be a pain to keep the code that invokes the sprocs in sync with the database if things are evolving rapidly. The points about producing efficient queries could count as premature optimization. At the end of the day, there is no substitute for benchmarking performance under realistic conditions.
I can think of several good reasons for stored procedures:
For a small application that's maintained by one person, stored procedures are probably overkill.
There are significant associated performance improvements on the SQL Server side of things if you use stored procedures in appropriate circumstances.
Stored procedure support for LINQ to SQL was included partly for compatibility with existing systems. This allows developers to migrate from a sproc-based system to a fully LINQ-based system over time, sproc by sproc, rather than forcing developers to make a rush to convert an entire system all at once.
Personally, I don't care for LINQ. I like a separation of the data manipulation stuff and the code stuff. Additionally, the anonymous types that are generated from a LINQ statement cannot be passed-off to other layers of an n-tier application, so either the type needs to be concretely defined, or the LINQ call needs to be made in the UI. Gack!
Additionally, there are the security concerns (whatever user the LINQ code is calling into MS SQL Server under needs to have unfettered access to the data, so if that username/password are compromised, so is the data).
And lastly, LINQ to SQL only works for MS SQL Server (as it comes from MS).
Sprocs have their uses, just like using LINQ does. IMO if an operation is performed multiple times in multiple places then it's a good candidate for "refactoring" into a Stored Proc, as opposed to a LINQ statement that is repeated in different places.
Also, and this is probably blasphemy to a lot of people here, sometimes you should put some logic into the database and then a sproc comes in handy. It's a rare occurrence but sometimes the nature of business rules demands it.
Stored Procedures are useful in many cases, but in General if you are using an ORM you should let the ORM generate the SQL for you. Why should we have to maintain at a minimum of four stored procedures (insert update delete and a single select) for each table.
With that said as people pointed out there are security benefits to using stored procedures. You won't have to grant users read/write to the tables, which is a good protection against SQL Injection.
Stored Procedures are also useful when the logic used to retrieve data is fairly complex. You typicaly see this more in Reporting Scenario's and in which case your probally not using Linq2Sql or some other ORM.
In my opinion if your not generating your SQL but essentially hardcoding it within an app tier, then that should be refactored into stored procedures, and yes there are always exceptions to any rules but in general.
One use of a stored procedure in Linq2Sql might be if you have multiple servers, and are linking to them, you could use a stored procedure to expose data from that other server and manipulate it. This would hide the multiple servers from your application.
Some things can't be done without stored procedures. For instance, at my previous job, there was a stored procedure that return the current value from a row, and incremented it in the same atomic operation such that no two processes every got the same value. I don't remember why this was done instead of using auto-increment, but there was a reason for it.
Reason : Large amounts of data to move from one table to another.
Let's say that once in a while you have to archive items from one table to another or do similar things. With LINQ that would mean to retrieve let's say one million rows from table A into the DBMS client and then insert them into table B.
With a stored procedure things work nice, in sets.
Lots of people have been getting by just fine without them for some time now. If you can do your work securely and efficiently without them, don't feel guilty about going with pure L2S. We're glad to be rid of them @ my shop.
You certainly don't "need" stored procedures. But they can come in handy if your domain model requires a complex aggregate Entity and you don't have the luxury/flexibility to modify your database tables to fit your domain model. In this case using Linq-to-SQL or another ORM might result in a very poorly performing set of database calls to construct your Entity. A stored proc can come to the rescue here.
Of course, I would advocate using a methodology or process like TDD/BDD that provides you the flexibility to modify your database tables as needed without much pain. That's always the easier, more maintainable path in my opinion.
Simple example:
select * from Products where GetCategoryType(CategoryName)=1
GetCategoryType can run really fast, because it runs on the DB server. There's no Linq to SQL substitute for that as far as I know.
I'm coming rather late to this thread. But depending on who you talk to, Linq to SQL is either dead, very dead, or at best a zombie.
In addition, no single tool suits every situation - you need to choose the right tool for the specific job in hand:
