none
When to use Stored Procs in Linq to SQL RRS feed

  • Question

  • Hi everyone ,
     I'm looking to use Linq to SQL for my ASP.NET web app. However, I'm not quite sure what the best way to use it is. I've decided to replace my DAL with Linq, which was a difficult decision as I prefer to abstract my code as much as possible. From what I've read of Linq, it seems to be creating and executing SQL queries very efficiently, so when do I use Stored Procedures? Are there any situations where SP's are recommended over the traditional parameterized queries when using L2S?
    In context of my project, user's will be allowed to insert, update and delete rows from certain tables, so I'd also like to keep it as secure as possible.
    Any help would be much appreciated.
    Thank you
    Tuesday, September 1, 2009 9:45 PM

Answers

  • Hi,

    Some situations where you might want to use stored procs from L2S:

    1) Existing db, stored procedures already existing and you don't want to re-implement the already existing queries/logic.

    2) Sometimes, in some companies, DBAs will not allow apps to read and/or write directly to tables. Everything goes through stored procs to allow the DBA to maintain absolute power in his/her little fiefdom.

    3) Complex queries where you need to use optimizer hints (join hints, index hints, locking hints etc), and/or where you want to bypass the L2S query generator for some other reason.

    4) If you for some reason need to use server-side cursors for some operation. Rare, but it does happen.

    5) For server-side sql-dml. L2S issues inserts/updates/deletes on a row basis in its' out-of-the-box implementation so if you want to do "insert into ... select ... from ...."  or "update/delete ... set ... where ..." touching many rows at once then you may want to put that in stored procs.*

    (* = .. This can be worked around though - I wrote a class that adds sql-dml support based on L2S. Will soon republish on my blog (it is undergoing an overhaul - as is my blog). )

    Other than that, L2S generally generates very efficient SQL so the above is for exception cases IMO...

    Best regards,
    Kristofer
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by RegX Thursday, September 3, 2009 12:18 PM
    Wednesday, September 2, 2009 1:02 AM
    Answerer

All replies

  • Hi,

    Some situations where you might want to use stored procs from L2S:

    1) Existing db, stored procedures already existing and you don't want to re-implement the already existing queries/logic.

    2) Sometimes, in some companies, DBAs will not allow apps to read and/or write directly to tables. Everything goes through stored procs to allow the DBA to maintain absolute power in his/her little fiefdom.

    3) Complex queries where you need to use optimizer hints (join hints, index hints, locking hints etc), and/or where you want to bypass the L2S query generator for some other reason.

    4) If you for some reason need to use server-side cursors for some operation. Rare, but it does happen.

    5) For server-side sql-dml. L2S issues inserts/updates/deletes on a row basis in its' out-of-the-box implementation so if you want to do "insert into ... select ... from ...."  or "update/delete ... set ... where ..." touching many rows at once then you may want to put that in stored procs.*

    (* = .. This can be worked around though - I wrote a class that adds sql-dml support based on L2S. Will soon republish on my blog (it is undergoing an overhaul - as is my blog). )

    Other than that, L2S generally generates very efficient SQL so the above is for exception cases IMO...

    Best regards,
    Kristofer
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by RegX Thursday, September 3, 2009 12:18 PM
    Wednesday, September 2, 2009 1:02 AM
    Answerer
  • Hi,
      Thank you for the reply. I haven't implemented anything yet, so there's no legacy structure and I'm working on my own, so I guess that makes me the DBA :D. To clarify, would you recommend using normal Linq queries for situations where a new user is created, or the user creates a new 'group' (to allow others to join) since these are single increments? And for batch/multiple rows, such as "Delete all groups which are empty", you would suggest a stored proc?
    Thanks again for the help, and I'll keep an eye out on your blog for more info :)
    Wednesday, September 2, 2009 1:37 AM
  • To clarify, would you recommend using normal Linq queries for situations where a new user is created, or the user creates a new 'group' (to allow others to join) since these are single increments?

    Yes

    And for batch/multiple rows, such as "Delete all groups which are empty", you would suggest a stored proc?


    Yes, if there is a potential that the delete statement cover a lot of rows and you have no need to pull them back to the client in order to delete them. E.g. "delete all orders older than 5 years", "add 10% discount for all order item rows for product xyz", etc.


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Thursday, September 3, 2009 8:00 AM
    Answerer
  • Cool, I get it now. Thanks for the help!
    Thursday, September 3, 2009 12:18 PM
  • This thread was from 09.  Is there still no ootb linq equivlent for set-based operations?
    Monday, August 22, 2011 5:04 PM