One thing you might have noticed that we do is SQL. We do a lot of SQL, so you’d think we would have figured out the best way of hooking up applications to databases.

To an extent, we have become so familiar with the requirements of database-driven development that we have come to accept the tools that we have as being adequate. Even ODBC.

Obviously we’ve long ago moved past PHP functions like mysql_fetch_row. We expect things like sanitizing and escaping, multiple vendor support, query abstraction, and so on. We look back fondly on the clumsy simplicity (and scope for injection) of things like this:

PHP

mysql_query( "SELECT * FROM T_Shirt WHERE color = '{$favorite_color}'" );

Nowadays we’re more likely to fetch records through a framework API, such as this use of Zend Framework’s Zend_Db:

PHP / Zend Framework

$db = Page::getDb();
$select = $db->select();
$select->from( 'T_Shirts' );
$select->where( 'color = ?', $favorite_color );

return $db->fetchAll( $select );

Because the query is broken down into a set of calls we can pass the select object around before we do the fetch. This is surprisingly handy.

But what else is out there that might be better than what we’re doing now? What would be the next step, the thing that would make Zend_Db look like mysql_query?

If we were to not care about efficiency or robustness, we could use Rails’ ActiveRecord

RoR / ActiveRecord

Shirt.find(:all, :conditions => ['color = ?' favorite_color] )

Which gives us a list of objects. This is slightly better than getting a bunch of arrays, but the syntax is a one-shot way of fetching – there isn’t an easy way to assemble a query progressively.

What about Java? Java has Hibernate.

Java / Hibernate

List shirts = session.createQuery( "from T_Shirt as shirt where shirt.color = ?" ).setString(0, favorite_color).list();

Which again gives us an set of objects. Like most ORM products, Hibernate tries to sanitize SQL, with the effect of not being terribly helpful. At least Hibernate lets us defer the actual fetch.

Let’s look at what Microsoft is doing. (If you irrationally hate Microsoft, you might want to stop reading now. Here’s a picture of Richard Stallman instead.)

The evil empire’s latest database connectivity approach is LINQ. LINQ is implemented as a language extension, rather than as a set of functions.

C# / LINQ

var query = from t in T_Shirt where color == favorite_color select t;

Notice the query isn’t a function or a quoted string; it’s a language construct, like a select or a foreach. BFD you might think. Keep reading.

C# / LINQ

var next_query = from t in query where brand == 'Prada' select t;

We’re actually using the same query engine on an array; the enumerable object returned by the first query is the datasource for the second query. LINQ extends to XML, MySQL and ADO.NET

The really cool thing is how this works with (for example) Excel. The following code uses LINQ to Excel, an open source project.

C# / LINQ

XlsWorkbook book = new XlsWorkbook("TestData\\100.xls");

var sheets = from s in book.Worksheets
                 where s.Name == "100"
                 select s;

This level of integration works both ways, so you can generate .xls files using selects too.

This isn’t just a slightly better way of fetching records; it’s a game-changer. Having been a Mac user for twenty years it’s a little odd being a Microsoft evangelist, but this is compelling stuff.