LINQ to SQL and the Coming Apocalypse

I'm going to say it, and I'm going to say it for everyone to see: LINQ TO SQL SCARES THE HELL OUT OF ME.

Does anyone remember this from classic ASP?

<%
Set rs = Server.CreateObject("ADODB.RecordSet")
param = Request.Form("lastname")
q = "SELECT * FROM personnel WHERE lastname LIKE '" & param & "'"
rs.Open q, "DSN=mydsn;" 
if NOT rs.EOF then
     while NOT rs.EOF
          Response.Write rs("firstname") & " " & rs("lastname") & "<BR>"
          rs.MoveNext
     wend
end if 
%>

LINQ to SQL is giving me flashbacks to this kind of code.

No, of course code written in LINQ to SQL won't look anything like that. But it will look like this:

HookedOnLINQ db = 
     new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");   
     var q = from c in db.Contact
           where c.DateOfBirth.AddYears(35) > DateTime.Now
           orderby c.DateOfBirth descending
           select c;   
     foreach(var c in q)
       Console.WriteLine("{0} {1} b.{2}",
                   c.FirstName.Trim(),
                   c.LastName.Trim(),c.DateOfBirth.ToString("dd-MMM-yyyy"));

So, what we potentially have here is database code mixed in with our business code. Further, we have no guarantee that this code will not appear in the .aspx page.

What really disturbs me about LINQ to SQL is that it looks like people will begin to use it to do things that really should be left to the database. Looking at the specific code example above, is there any good reason that this couldn't have been done with a stored procedure? I mean, after all, stored procedures are compiled, provide additional security, and aren't subject to some sleepy programmer doing a global search and replace in the IDE and borking the code.

Now, I realize that LINQ to SQL has support for stored procedures. But I'm willing to bet that the vast majority of organizations are going to use that support in conjunction with the syntax shown above to produce truly horrendous code that completely negates the tremendous power available to them in the database.

Database technology has evolved over decades to be extremely efficient at what it does: indexing, sorting, selecting, inserting, updating, and so on. We will never be as efficient doing it client side as the database will be on the server side. Ignoring that power and trying to do it in code is an exercise in futility. The lesson we need to bear in mind here is this: let the database do what it does best, and let the code do what it does best.

Sadly, I don't have a whole lot of confidence that this is going to be the case in many places, because LINQ to SQL makes it far too easy to do the database's job. For crying out loud, you can do a WHERE and an ORDER BY--which should happen in the database so you can take advantage of the indexes--in the code. (Perhaps, under the hood this gets done by generating SQL. Fine. But why am I essentially writing SQL statements in code again?! WHY!? Get that SQL out of the damned code! It doesn't belong there! SQL belongs in the database!)

Now, suppose, for instance, that LINQ to SQL is 100% bug free on its first iteration. Let's assume that it generates flawless SQL to query your database when you write that code. It still has to pass dynamic SQL. That means you can't take advantage of compiled stored procedures. It also means that you have to repeat that code if you want to reuse it--unless, of course, you're savvy enough to refactor your code base to do so. But let's be honest: the folks I'm worried about here probably aren't smart enough to refactor their code base because they're likely in a rush to get the code out as quickly as possible, and refactoring isn't a big ticket item for them. The Single Responsibility Principle likely hasn't bubbled to the top of their list of grave concerns yet.

Why this becomes a serious concern is simple: Eventually, someone has to maintain that code.  

How many of us have nightmares about working with someone else's lamentably bad ASP code that had embedded SQL statements in it? Remember how horrible that was? Remember trying to search all the files to figure out where the SQL was? Which files touched which tables?

Why on earth do we want to go back to that?

Sure, sure; someone, somewhere, has an utterly compelling need for LINQ to SQL. They absolutely, positively must have it. Their business will collapse if they don't have it. Problem is, as I see it, this is going to be abused like a box of needles and a ton of heroin at a recovery clinic. And it's everyone else who's going to pay the price.

So, in closing, I'll just say this:

DOOOOOOOOOOM!