LINQ and Stored procedure

LINQ provides a query syntax used for querying different data sources like SQL server, Oracle, Collections etc. Type checking is done at compile time in LINQ. DOT NET framework languages like C# and VB.NET has IntelliSense support for LINQ in Visual Studio. Whereas, On the other hand, a pre-complied set of SQL statements stored on RDBMS is stored procedure. There is no IntelliSense support for stored procedure in Visual Studio. The execution of stored procedure is performed on server side and results are only returned to client side after performing a set of actions.

Comparison of LINQ and Stored Procedure

• Stored procedure execution is faster than Linq as execution plan of stored procedure is predictable and takes full advantage of SQL features. The database uses the cached execution plan for executing stored procedure whenever it is executed second time.

• The run time errors are avoidable in LINQ since they provide compile time type checking as compared to stored procedure.

• As compared to stored procedure the debugging in Linux is through .Net debugger.

• Linq supports Multi-threading whereas stored procedure does not.

• Stored procedures are re-written for multiple databases whereas Linq have common query syntax.

• Complex sql queries are best written through stored procedure as compared to Linq.

• Linq based application can be simply and easily deployed in comparison to stored procedure, as in stored procedures, deployment process require a SQL script whereas Linq compilation results into the DLLs. Hence only DLLs needs to be deployed.   

• Stored procedures is better way for bulk insert and update operations.