question

Mark avatar image
Mark 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?
stored-proceduresdeveloperlinq-to-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
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
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
+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 :)
0 Likes 0 ·
Some claim that the LINQ syntax is actually easier to use than SQL Server's (at least in some cases). (IIRC, I've seen examples too.) Do that come into play as well?
0 Likes 0 ·
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.
0 Likes 0 ·
I can understand that Scot.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Assume you have all the access you need to use either method. The purpose of the question was basically to find out when LINQ is used as opposed to stored procedures. However, maybe lack of access is part of the answer.
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.