Linq to SQL - Dynamic SQL vs Stored Procs

Linq to SQL (L2S) is an exciting technlogy released with Visual Studio 2008. It is more or less an Object Relational Mapper (ORM) that easily integrates Sql Server databases to your C# object models. It can either create the database from a model, or the model from a database, or you can retrofit you existing Plain Old C# Objects (POCO).

I intially rubbished it, but after revisting and doing some more reading, I have become a convert. Like most things in the software development world, it's not a magic bullet, but is a great tool to have on your belt.

The interesting thing with L2S, is that it can generate the queries for you at runtime, or you can invoke Stored Procedures.

Now, standard and historical rule of thumb states that Stored Procedures are the answer to almost any question, but with L2S this may not be the case.

For a start, parameterised stored procedures are no faster than dynamic sql. Before you flame me, read this. The other reason I beleive that L2S could be more efficient than SP's is that because it generates the queries on a per instance basis, it can retrieve only the data you need at that point in time. Take this scenario:

You have some customer data you want to retrieve. In this case you only need, custId, custName and orderStatus. You already have a stored proc that retireves all of the customer columns. In my experience, given a pool of 100 developers, at least 75 of these will just reuse the original SP and filter at the C# end. Under L2S you are already prefiltering at the C# end and only hitting the fields in SQL Server you need.

Updates are the same, L2S will only update the fields it needs to, whereas most of the time you will just use a generic update SP that will send back all the fields changed or not.

I would not rule out SP's entirely. A lot of the time it is good, having a central repository for data interactions, especially for larger applications. Also, if you have a good DBA on team, then SP's would probably be a better option.

The point is that with L2S you have options, and don't automatically rule out dynamic sql in your next application.