Home  /  Questions  /  Question

121   96.7
May 06, 2010

LINQ-to-SQL and Stored Procedures

I have a side project where most of the operations will be simple data retrievals (the data is automatically inserted at regular intervals through another process -- i.e. a scheduled task).  The application will be used as an ad-hoc reporting / querying tool.  There will be minimal joins, so the SQL statements are all pretty simple.  It is important that the operations work fast and efficient.

My client is suggesting we use LINQ-to-SQL combined with stored procedures. My question is this: if we decide to use stored procedures, is LINQ-to-SQL the right tool?  Or, would it be better to use classic ADO.NET?  Are there any advantages to using LINQ-to-SQL over ADO.NET?  How about performance, ease-of-use, etc?  Thanks in advance.

288   99.8
May 10, 2010

Some advantages of LINQ over sprocs:

  1. Type safety: I think we all understand this.
  2. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this support. It would be MUCH harder to do this data access code that simply calls sprocs.
  3. Debugging support: I can use any .NET debugger to debug the queries. With sprocs, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn't enough).
  4. Vendor agnostic: LINQ works with lots of databases and the number of supported databases will only increase. Sprocs are not always portable between databases, either because of varying syntax or feature support (if the database supports sprocs at all).
  5. Deployment: Others have mentioned this already, but it's easier to deploy a single assembly than to deploy a set of sprocs. This also ties in with #4.
  6. Easier: You don't have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the sprocs. This is related to #3 and #4.

Some disadvantages of LINQ vs sprocs:

  1. Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.
  2. Less flexible: Sprocs can take full advantage of a database's featureset. LINQ tends to be more generic in it's support. This is common in any kind of language abstraction (e.g. C# vs assembler).
  3. Recompiling: If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Sprocs can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.

Security and manageability are something that people argue about too.

  1. Security: For example, you can protect your sensitive data by restricting access to the tables directly, and put ACLs on the sprocs. With LINQ, however, you can still restrict direct access to tables and instead put ACLs on updatable table views to achieve a similar end (assuming your database supports updatable views).
  2. Manageability: Using views also gives you the advantage of shielding your application non-breaking from schema changes (like table normalization). You can update the view without requiring your data access code to change.
Keeping this in view. you can decide accordingly.
 1 comment
this answer appears to have been ripped off a 2008 post in stack overflow - http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures --- Bala Ram  Jul 14, 2011