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

avatar image

2.6k 24 27 31

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

3 answers: sort voted first

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 [/Edit]

more ▼

answered Jul 27, 2011 at 12:46 PM

avatar image

24.7k 3 10 19

  • 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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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.

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: 6112 times

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

Copyright 2016 Redgate Software. Privacy Policy