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.
I used to be a big sproc guy, but I'm starting to lean towards LINQ as a
better alternative in general. If there are some areas where sprocs are
clearly better, then I'll probably still write a sproc but access it using
LINQ. :)
