Linq over Stored Procedures

Question

What are some pros and cons of using linq over stored procedures?

Answer 1

Since nobody's added a CON - I'll suggest one here...

Stored Procs offer you the ability to deny select/insert/delete/update on your base tables and views, so you could have potentially better security using stored procs as you could using Linq to SQL. By granting exec permissions only on your procs, you have a smaller surface area to manage for security.

Of course - you can still use Linq to SQL and stored procs together - maybe that would be the better option.

Answer 2

LINQ is a wonderful addition to the .Net Framework, it does, however, have it's limitations. As of yet LINQ does not yet support all of the SQL Syntax (though I'm sure they're working on it). Also, with LINQ there is no clean way of having it process multiple tables and give us only the data we need. With LINQ you would have to retrieve all the data, then keep what you want and throw the rest out, thus transmitting more data than is actually needed, which is a performance issue.

If all you're doing is simple INSERT, UPDATE, and DELETE statements LINQ is the way to go (in my opinion) and all the optimization is done for you, for more complex work I would say to stick with stored procedures.

Answer 3

From my perspective, the primary value of stored procedures has been eliminated with LINQToSQL. For a long time, the key value of using stored procedures was to encapsulate the SQL in a form where you would naturally use parameterized queries. This provided a layer of security to the developer with respect to SQL injection. Since LINQToSQL queries are, by default, parameterized I find that my use of stored procedures has been reduced dramatically.

That's not to say that there isn't still a place for them, but now I feel that you should only use them when they provide significant value over a parameterized LINQ query in terms of less complexity or increased performance, perhaps because of the server's ability to optimize for the procedure. Removing over dependence on stored procedures, I feel, results in a more maintainable code base as most of the code is located in your IDE instead of being split between your database and your IDE.

Note that you can still use stored procedures with LINQToSQL. I just don't find much value in doing so. Actually, I can't think of a single stored procedure that I've written since switching to LINQToSQL, though I have written a few table-valued functions to perform specific searches. These get dropped onto my DataContext and appear as methods that I can invoke to get the appropriate entities from the DB using the function.