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?
more ▼

asked Jul 27, 2011 at 12:37 PM in Default

Mark gravatar image

2.6k 23 25 27

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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
more ▼

answered Jul 27, 2011 at 12:46 PM

KenJ gravatar image

20.3k 1 4 12

+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 :)
Jul 27, 2011 at 02:35 PM Andomar
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?
Jul 27, 2011 at 02:46 PM Mark

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.
Jul 27, 2011 at 03:47 PM KenJ
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.
Jul 27, 2011 at 04:47 PM Scot Hauder
I can understand that Scot.
Jul 28, 2011 at 08:17 AM Mark
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Jul 27, 2011 at 02:25 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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.
Jul 27, 2011 at 02:32 PM Mark

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.
Jul 27, 2011 at 02:35 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 27, 2011 at 12:37 PM

Seen: 3196 times

Last Updated: Jul 27, 2011 at 12:37 PM