Welcome to EMC Consulting Blogs Sign in | Join | Help

Simon Evans' Blog

My blog covers the technology areas I focus on here at EMC Consulting, namely Architecture using the .NET Framework, ASP.NET, WCF, WCF Data Services and Windows Azure Follow me on twitter @simonevans

LINQ to SQL: Let the debate begin

So I just got back from TechEd Barcelona, and I had a great conference in no small part due to the people I went with: Merrick Chaffer, James Dawson and Paul McMillan. This is a selection of some of Conchango's finest from the world of .Net development, infrastructure and SQL Server. One of the reasons the conference was so much more enjoyable in the company of my colleagues was that I got as much out of the conversations we had outside of the sessions as I did from what I learnt in the sessions and labs.

Of all the subjects covered at this year's TechEd, none had more coverage than LINQ, and between us, none had more debate than the impact LINQ has on our worlds. This is a conversation I can see playing out across the Developer / DBA divide the world over, as the majority of solutions developed require some sort of data access to a database. David Chappell made a comment in his REST vs SOAP session (which was probably the best session I went to) that struck a real chord with me; he said "people are only passionate when something is in doubt" Conversations of this nature can tend to take a slightly religious standpoint, so I'll try to bear in mind David's comments.

So with LINQ to SQL (and LINQ to Entities) the most hotly debated point was around the auto generation of LINQ's dynamic SQL vs. using stored procedures. In the old days, one point favoring of using stored procedures was the performance gain from a cached execution plan. But since SQL Server 2005, dynamic SQL is also cached, so this reason for using stored procedures has largely gone away. One benefit dynamic SQL has over stored procedures is that you only select what you need in all cases. In a typical scenario with stored procedures and a DAL populating an object model, stored procedures will tend to be reused even when all of data is not used by the calling DAL method. There ways around this, but it largely involves an ever increasing list of stored procedures (GetObjectByX), make the solution less manageable.

What became clear in our debates on this topic is that really all depends on who you trust more: a developer writing a stored procedure or the LINQ to SQL engine? I began the week at TechEd a skeptic of LINQ to SQL, but the more I looked at what the engine produced, the more I began to trust it. Sure it won't do a better job than a highly trained SQL expert (like Paul), but is it good enough and better than your average developer? From what I have tried I would say the answer is yes. And the bottom line is, when you need that extra performance out of a finely tuned stored procedure, you can still use them.

Published Sunday, November 11, 2007 2:15 PM by simon.evans

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

London coder said:

Who [do] you trust more: a developer writing a stored procedure or the LINQ to SQL engine? LINQ

December 18, 2007 9:13 AM
 

Paul Massen said:

Hi Sevens,

another nice article,particularly "But since SQL Server 2005, dynamic SQL is also cached", this has got me digging at the architecture, as my first thought with LINQ ,"yes I can see what you are doing but looks like emmbedded SQL".

February 1, 2008 9:12 PM
 

will said:

Actually it is since MS-SQL 2000 that dynamic sql has been cached the same as stored procedures.

All the old wives tales of using stored procedures for CRUD statements are from MS-SQL 7.  Once you do testing you will find out that stored procedures are slower then parameterized dynamic SQL the reason is that all the checks you need to implement for stored procedure CRUD is really, really slow.

March 22, 2008 6:57 PM
 

Jez said:

Hi Simon,

Nice article.

I don't have any issues with dynamic SQL vs stored procs.

My concerns at present are over the maintenance of a solution created with LINQ to SQL.  Dragging and dropping a small number of related tables onto a design surface creates a data context which then allows you to code against objects - all fine and dandy.  

However the codegen is all hidden and digging deeper there seems to be lots of it but relatively little you may ever want/need to use.  

What happens when you have a real world scenario with dozens or hundreds of tables?   Multiple data contexts with overlapping tables?  What about when the schema changes?   I would have liked to have seen a separate model synched with the database which could allow for tuning the code output or providing runtime metadata.  

April 16, 2008 3:10 PM
 

ahtesham said:

I am using linq 2 sql store procedures . I didn’t change the return type of any store procedure so it is returning isingleResult<storeprocedureResult>

I make a class ”mydataconetcion” which use the datacontext class object and I am getting all store procedures in that class by

Datacontext db=new datacontext();

One example of my store procedure is

public ISingleResult<dashCommerce_Store_FetchCategoryManufacturersResult> getCategoryManufecturer(int catId)

{

ISingleResult<dashCommerce_Store_FetchCategoryManufacturersResult> CategoryManufecture = db.dashCommerce_Store_FetchCategoryManufacturers(catId);

return CategoryManufecture;

}

In my web pages I make object of mydataconetcion class

Mydataconetcion dc=new mydataconetcion();

I am storing the result  in var then by foreach I am traversing it . or I simply bind it to my gridview and repetors and so on .

I am doin this

Var selectedProduct =dc. getCategoryManufecturer(7);

CatGridview.datasource= selectedProduct;

It works fine but now I want something like this

If (selectedProduct!=null )

{

CatGridview.visible=true;

CatGridview.datasource= selectedProduct;

}

Or empty or what ever condition that tell me that there is any value in  selectedProduct

So help me out on this how can I make sure that there is any value or not

May 22, 2008 8:42 AM
 

Memoirs of a Plumber said:

Simon Evans some time ago questioned if given the parametric nature of queries generated by ORMs, stored

November 10, 2008 11:20 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About simon.evans

Simon is a Managing Consultant for Conchango in the UK, part of EMC Consulting Services. He is an expert in .NET development, and more specifically in WCF and ASP.NET, having participated in several Microsoft early adoption programs. Simon believes deeply that a broad understanding of key technology concepts is an essential foundation to being a gifted designer and builder of solutions.
Powered by Community Server (Personal Edition), by Telligent Systems