asked
LINQ vs Stored Procedure
As a developer, how do you know when you can use LINQ-to-SQL and when you need to use a stored procedure?
With the premise that you're in a shop that's open to either... Based on my own experience, stored procedures work better for complex queries or queries that need to be specifically tuned. LINQ-to-SQL does okay generating relatively simple queries, but begins to fall apart when the query gets complex. It also precludes doing any query tuning or adjustment, aside from index creation, without redeploying your code: something that has proven useful time and again in my shop. I'm sure others will have had good luck with LINQ when generating complex queries. Results can certainly vary by environment - it depends, right? :) For a hybrid approach, I seem to recall reading that you can have LINQ-to-SQL use stored procedures, though I'm not sure how gracefully it all fits together. **[Edit]** Here is part 6 of a multi-part LINQ-to-SQL series by Scott Guthrie that covers using LINQ with stored procedures - [
http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx][1] **[/Edit]** [1]:
http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
Never use LINQ-to-SQL. There, that was easy. Unless you like performance problems and maintenance headaches. I do like LINQ for XML and other collections, though. Also remember LINQ-to-SQL defaults to serializable (or it used to when I was exposed to it) so you will probably want to set the isolation level in your code to something that will cause fewer contention problems, perhaps read committed.
+1 Using linq2sql to call a stored procedure is practical, but it has one drawback. If you call more than 10 procedures, you end up with a 30-100 ms delay as linq2sql keeps creating deserialization assemblies on the fly. An unpleasant surpise if you move your website to production :)
I hear a lot of developers claim personal preference to using LINQ syntax over querying the database with any type of SQL. That is worth consideration when deciding between the two... unless the performance/complexity issues with LINQ generated SQL dictate a TSQL approach.
What kind of access to the servers do you have? More specifically, what kind of access to the servers do you have in production? The level of access accorded your application could dictate how you need to do your programming.
Oh, I misunderstood the point of the question. Yeah, my answer still stands, but I'd go more with @KenJ on the larger scope. For most CUD type queries, any of the code generation schemes do a great job. For the R part of the process, they break down rather quickly as the complexity increases. All that assumes actual ORM processing and not an OOM design.
