The LINQ (.NET Language Integrated Query)
Project is a Microsoft initiative to standardize data access across not only
data sources but also development. A number of developers who have examined LINQ
have proclaimed that it will revolutionize database application 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 standardized 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 standardized method from
any .NET-compliant language such as C#, VB.NET, and others.

Weekly SQL tips in your inbox

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

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
organization 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 analyzed
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.