Should I use SqlCommand's or "Linq to SQL"

In all of my previous websites I have worked with MsSQL using the SqlCommand class to interact with the database itself. I have seen a lot of hype about "Linq to SQL". I think that I am okay with the syntax and usage.

Why should I use "Linq to SQL"? What benefits does it have over the SqlCommand approach? Does this mean that my web application is going to be doing all of the query processing? Is it not best to have the SQL server do the hard work?

Or, do you guys recommend another approach?
LVL 13
Who is Participating?
nmarunConnect With a Mentor Commented:
One way is to use Stored Procedures with SqlCommand. This way your sql commands are pre-compiled and they are in a separate layer than your .net code. Even if there's a slight change in your table structure, you might not have to change your .net code.

The benefit of using LINQ is the flexibility it offers in creating dynamic types. But the downside would be that you would be passing adhoc queries (slightly less efficient). It's also not sure that you'll find the difference in the performance lag because of passing adhoc queries.

Personally I prefer using Stored Procedures with SqlCommand.
aibusinesssolutionsConnect With a Mentor Commented:
The main thing about Linq is that you can use it for anything, not just "Linq to SQL", I'm not sure how that one class gets all the hype.  Basically, if you learn to code with LINQ, then you will be more comfortable working with LINQ to SQL, otherwise I would stick to what you are comfortable with.

If you don't know much about LINQ, LINQ stands for Language Integrated Query, you can use it on any enumerable object, meaning you can group by, sort by, filter by, etc. on any enumerable.

Here is an example for working with an Array.
Dim values() As Integer = {43, 12, 0, 75, 433, 33, 76, 21, 5, 8, 320}
Dim query = From i In values WHERE i mod = 2 OrderBy i Select i

Or lets say you want to query some files on your computer.
Dim files = From file in GetFiles("c:\MyDirectory",".doc") OrderBy file.Name Select file

Or lets say you have a list of names in a List object, and you just want names that start with P
Dim Names = From x in MyList where x.StartsWith("P")  select x

The syntax is pretty SQLish, but as you can see it can be a real time saver when working with enumberables.
AllamzConnect With a Mentor Commented:
Hello numberkruncher,

please note that Microsoft announced in a conference  that "LINQ To SQL" project will not be completed.

so i suggest u complete using SQL Command :)

BEst Regards,

Mohamed Allam
Senior Solution Developer
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

numberkruncherAuthor Commented:
Thanks for all of your input. I hadn't realized that Microsoft have given up on "LINQ to SQL".
numberkruncherAuthor Commented:
Thanks all!
@Allamz:  I'm not sure where you got your information, but MS is not dropping Linq to Sql, they will continue to update it, but they want people to start using the Entity Framework as soon as .NET Framework 4.0 comes out.  That being said, the EF "will be" the "preferred" method of connecting to SQL as of 4.0, they will continue to support Linq to SQL.

Here is a quote from the ADO.NET Blog

"Were making significant investments in the Entity Framework such that as of .NET 4.0 the Entity Framework will be our recommended data access solution for LINQ to relational scenarios.  We are listening to customers regarding LINQ to SQL and will continue to evolve the product based on feedback we receive from the community as well." - Tim Mallalieu, Program Manager, LINQ to SQL and Entity Framework
numberkruncherAuthor Commented:
That sounds interesting, I will take a further look into the new Entity Framework.

i've attended a training in Barcelona 2 months ago and i've met with the LINQ Team Leader and he told me that they won't proceed in the LINQ To SQL as they are developing a new framework and that doesn't mean that they'll cancel it. it'll be there with support but without new features, etc...


Mohamed Allam
Senior Solution Developer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.