LINQ-to-SQL vs stored procedures? [closed]

by

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. 🙂

More Related Contents:

  1. Linq: adding conditions to the where clause conditionally
  2. LINQ Between Operator
  3. LINQ across multiple databases
  4. Learning about LINQ [closed]
  5. How can I reject all changes in a Linq to SQL’s DataContext?
  6. LINQ with SQLite (linqtosql)
  7. How to create a dynamic LINQ join extension method
  8. LINQ not updating on .SubmitChanges()
  9. How to dynamically add OR operator to WHERE clause in LINQ
  10. LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator
  11. Linq To Sql – Dynamic OrderBy – Case When
  12. Is there a pattern using Linq to dynamically create a filter?
  13. Linq to SQL how to do “where [column] in (list of values)”
  14. Multiple WHERE clause in Linq
  15. How do I write this cross apply query in LINQ-to-SQL?
  16. Point connectionstring in dbml to app.config
  17. Building Dynamic LINQ Queries based on Combobox Value
  18. NHibernate vs LINQ to SQL
  19. Entity Framework: There is already an open DataReader associated with this Command
  20. Linq to Sql: Multiple left outer joins
  21. Entity framework linq query Include() multiple children entities
  22. How to view LINQ Generated SQL statements?
  23. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  24. EF 4: Removing child object from collection does not delete it – why?
  25. Custom Method in LINQ to SQL query
  26. Max or Default?
  27. How do I do a left outer join with Dynamic Linq?
  28. Greater Than Condition in Linq Join
  29. How to implement Unit of Work that works with EF and NHibernate
  30. Linq to SQL: How do I stop the auto generated object name from being renamed?

Leave a Comment