Is LINQ the future of database development?

I think LINQ has several hurdles to leap before we can adopt it seriously. However, LINQ does offer some interesting benefits, and as SQL developers and DBAs, it behooves us to learn about this new technology and its promises.

LINQ's benefits
LINQ's benefits include, most significantly, a standardised way to query not just tables in a relational database but also text files, XML files, and other data sources using identical syntax. A second benefit is the ability to use this standardised method from any .NET-compliant language such as C#, VB.NET, and others.

See LINQ in action
The following snippet illustrates (in C#) a simple LINQ code block, using the Northwind database as its target. To see LINQ at work, we'll begin with a simple C# 3.0 program that uses the standard query operators to process the contents of an array.

using System;
using System.Query;
using System.Collections.Generic;

class app {
  static void Main() {
    string[] names = { "Alpha", "Brian", "Connie", "David",
                       "Frank", "George",
                       "Harry", "Inigo" };

    IEnumerable<string> expr = from s in names
                               where s.Length == 5
                               orderby s
                               select s.ToUpper();

    foreach (string item in expr)
      Console.WriteLine(item);
  }
}

This program delivers this output:

ALPHA
BRIAN
DAVID
FRANK
HARRY
INIGO

This example illustrates the coding style of LINQ and the movement of code to the front end that, in my opinion, should reside in the back end (even though I will admit that the code is clean and relatively transparent).

What front end and back end means to me
I'd like to offer this quick aside to discuss the terms front end and back end, which refer to the classic client-server configuration. The back end is the database itself, which resides on a server. The front end refers to the collection of client applications that speak to the database of interest; these might include apps written in any of the popular programming languages (e.g., Visual Basic, C++, and PHP). When talking about the front end, we must also include applications such as Microsoft Access, Excel, and Word.

In a small organisation with fewer than 100 computers, say, these distinctions may seem academic, but my motto is "Design for success, otherwise you build failure in." That is why I want as much logic as possible to reside in the back end rather than the front end. Suppose I have a procedure or function that creates a recordset, qualifies the selection with one or more parameters, and then orders it by one or more columns. If I place the logic of that procedure in any given front end, then I will have to duplicate it in any new front end that is added next week or next month. Although we like to think that logic is independent of language, in practice, that is not the case.

Development has evolved from classic client-server to various forms of three-tier architecture (DLL-hell, SOAP, etc.). In these architectures, the front end talks to a middle tier which contains considerable logic and is addressable by all the front ends of interest. The middle tier speaks in turn to the database of interest. How you achieve this communication varies with the front end language and the middle-tier implementation, but suffice to say that this middle tier communicates with the database server and that the front end must only concern itself with talking to the middle tier. So I stretch the term back end to cover this architecture as well. My point here is that the front end should do as little as possible. If you use a middle tier, then move all the logic you can to that level, then reconsider your design and move all the logic you can to the back end.

My bias may be clouding my optimism
I must reveal that I hold this as a sacred principle: Whatever the back end can do, the back end should do. Given this bias, you can understand my reluctance to see database logic move into the front end.

Why am I so steadfast in this principle? Because, in my experience, several different front-end applications may hit a single database -- they could be Web sites, Visual Studio apps, Delphi apps, and so on. To the extent that code moves from the back end to the front end, you must duplicate and translate the instructions from one front end to another. This is fine if you decide to stay within Visual Studio .NET, but suppose that you or your employer has other ideas? Or, what if you're interested in porting this logic to other databases?

My perspective is not unanimous. I know several developers who argue that certain applications and situations demand ad-hoc, dynamically constructed queries. I hold that these situations have not been analysed sufficiently, but I am willing to be proven incorrect on this point. So let us examine the other perspective and assume that it is correct: There are N situations in which the only correct approach is to have the front end create a valid SQL statement, protect the code from SQL injection, and then pass the statement to the database engine.

Let us further assume that regardless of the correctness of either position, there are numerous developers who would like to develop their SQL instructions in the front end rather than the back end. It is this set of developers for whom LINQ is most interesting. To the extent that you subscribe to my sacred principle, you may find this movement toward the front end disturbing.

The jury is still out
It's too early to determine whether this new technology will be the next-best-thing in database development. For one thing, it still needs to be tested in the trenches of database and application development. I do think LINQ's multi-data source abstraction is very cool, but, as I've stated, I feel that the movement of code from the back end to the front end is inherently a mistake.

Only time will tell. But until that time when LINQ's advantages are proven (as compared to an equally functional database which contains the logic in the back end, leaving the front end with the trivial job of constructing calls to stored procedures and supplying the parameters they demand), I'm sticking with the "classic" approach: Whatever the back end can do, the back end should do.