What exactly are some benefits and drawbacks of utilizing linq over saved methods?

Since no one's added a Disadvantage - I'll suggest one here...

Saved Procs provide you with a chance to deny choose/place/remove/update in your base tables and sights, so you might have potentially better security using saved procs while you could using Linq to SQL. By granting professional permissions only in your procs, you've got a more compact area to handle for security.

Obviously - you are able to still use Linq to SQL and saved procs together - maybe that might be the greater option.

From the perspective, the main worth of saved methods continues to be removed with LINQToSQL. For any very long time, the important thing worth of using saved methods ended up being to encapsulate the SQL inside a form in which you would naturally use parameterized queries. This provided a layer of security towards the developer regarding SQL injection. Since LINQToSQL queries are, automatically, parameterized I've found that my utilization of saved methods continues to be reduced significantly.

That's not saying there is not still a spot for them, however Personally i think that you ought to just use them once they provide significant value on the parameterized LINQ query when it comes to less complexity or elevated performance, possibly due to the server's capability to optimize for that procedure. Getting rid of over reliance on saved methods, Personally i think, produces a more maintainable code base as the majority of the code is situated inside your IDE rather than being split involving the database as well as your IDE.

Note that you could still use saved methods with LINQToSQL. I simply don't find much value by doing this. Really, I can not think about just one saved method that I have written since switching to LINQToSQL, though I've written a couple of table-valued functions to do specific searches. These get dropped onto my DataContext and appearance as techniques will be able to invoke to obtain the appropriate organizations in the DB while using function.

LINQ is an excellent accessory for the .Internet Framework, it will, however, have it's restrictions. By yet LINQ does not support all the SQL Syntax (though I am sure they are focusing on it). Also, with LINQ there's no clean method of getting it process multiple tables and provide us just the data we want. With LINQ you would need to retrieve all of the data, then keep what you would like and toss the relaxation out, thus transmitting more data than is really needed, the industry performance problem.

If all you are doing is straightforward Place, UPDATE, and Remove claims LINQ is what you want (for me) and all sorts of the optimisation is accomplished for you, for additional complex work I'd tell stick to saved methods.

You will find a couple of a few things i added towards the discussion:

  • Linq is not as likely hitting a cached execution plan in SQL Server than the usual saved procedure. While compilation of fundamental choose claims is light, in additional complicated queries compilation can be very costly. (At large amount of factors get into causeing this to be statement true/false for the situation, but that is most likely for any different publish).

  • For those who have skewed data recompilation may really be considered a benefit prevent odd index use choices by SQL. Think "choose * FROM Person where LastName = @Letter" First pass where @Letter='Z' (allows say .25% of total people) versus @Letter='S' (6% of total people) can lead to extremely different execution plans.

  • Linq is effectively reintroducing random sql into our code. Granted its via a layer of abstraction as well as in a brand new language, but no more shall we be held calling "professional GetOrders @DateStart=@now @DayRange=7" rather I am writing out my table title, where clause, and order by.

  • Monitoring non-performant SQL claims to the code that produced the claims is much more diffcult compared to SP's. Inside a high volume atmosphere SQL profiler is frequently run regularly to locate non-performant queries (high CPU, reads, or duration). Since Linq abstracts the written text it creates it might be difficult to trace the sql to a particular location within the code, specifically in bigger programs.

  • Like a side note, at the appropriate interval Linq can call a particular saved procedure instead of producing it's own SQL. See http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx