LINQ Vs SPs

Posted by: NIKHAT SHAHIN on: December 20, 2010

• In: Asp.net | Technical Articles
• 1 Comment

Some advantages of LINQ to Sql over SPs:-

1. Type safety: One of the key features of LINQ is that it is type safe, so it becomes much easier to write queries that are type checked at compile time.

2. Debugging support: any .NET debugger can be used to debug the queries. With sprocs, you cannot easily debug the SQL.

3.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).

4.Deployment: It’s easier to deploy a single assembly than to deploy a set of sprocs.

5.Easier: 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. It provides a lot of design-time functionality which is a dream and compiles down pretty much to the same code as ADO.NET would, it just provides the neat ability to reference tables, columns and data exactly as if you’re coding to regular classes and methods.

Some disadvantages of LINQ vs stored procedure:

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.If there are some areas where sprocs are clearly better, then we can probably still write a sproc but access it using LINQ.

2.Less flexible: Sprocs can take full advantage of a database’s feature set. LINQ tends to be more generic in its 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.