Thursday, March 27, 2008

Expression Trees: Why LINQ to SQL is Better than NHibernate

In my last post I described how the Where()
function works for LINQ to Objects via extension methods and the yield statement. That
was interesting. But where things get crazy is how the other LINQ technologies, like LINQ to SQL use extension methods. In particular it’s their use of a new C# 3 feature called expression trees that makes them extremely powerful. And it’s an advantage that more traditional technologies like NHibernate will never touch until they branch out from being a simple port of a Java technology. In this post I’ll explain the inherent advantage conferred on LINQ technologies by expression trees and attempt to describe how the magic works.

What’s so Magic about LINQ to SQL?

LINQ to SQL (and it’s more powerful unreleased cousin LINQ to Entities) is a new Object Relational Mapping (ORM) technology from Microsoft. It allows you to write something like the following:

Which as you’d expect returns products from the database whose category is Beverages. But wait, aren’t you impressed? If not read over that code again, you should be very impressed. In the background that C# code is converted into the following SQL:

You could use HQL too, but both NHibernate options suffer from the same problem. Did you spot it?

The LINQ to SQL version is taking actual strongly typed C# code and somehow smartly converting it to useful SQL. The NHibernate version does the same thing, but always
using a weakly typed alternative. In other words the column “CategoryName” in NHibernate is a string. If it or its data type change
in NHibernate you won’t find out until runtime. And that is the beauty of LINQ to SQL: you’ll find more errors at compile time. And if you’re like me you want the compiler to find your mistakes before the unit tests that you (or your fellow developers) may or may not have written
do.

So you’re probably now wondering if you can put strongly typed C# in your where clause and it somehow magically gets converted to SQL, what’s the limit? If you put in a String.ToLower() or StartsWith() will it get converted to equivalent SQL? What about a loop or conditional? A function call? A recursive function call? At some point it has to break down and either return all products and filter them in memory or just fail right? Before answering those questions we need to understand what’s going on.

Understanding the Magic

The Magic happens in a class called Expression<T>. Expression takes a generic argument that must be a delegate and is usually one of the built in Func methods.
However the class can only be instantiated to a lambda expression. That’s right, not a delegate or anonymous method, only a Lambda expression. So in my deferred execution post where I explained what Lambda expression are, I said they were essentially syntactic sugar for an anonymous methods. Well, the emphasis is on the essentially, because they really aren’t sugar at all. When you assign a lambda expression to an Expression, the compiler, rather than generating the IL to evaluate the expression, generates IL that constructs an abstract syntax tree (AST) for the expression! You can then parse the tree and perform actions based on the code in the lambda expression.

ParameterExpression param = (ParameterExpression)expression.Parameters[0];// this next line would fail if we change the Lambda
expression muchBinaryExpression operation = (BinaryExpression)expression.Body;ParameterExpression left = (ParameterExpression)operation.Left;ConstantExpression right = (ConstantExpression)operation.Right;

This outputs “Decomposed expression: i => i LessThan 5”. The first line is the most
important. It defines an Expression that takes a delegate with a single int parameter and a return type of bool.
It then instantiates the Expression to a simple lambda expression. Incidentally this would also work if we defined our own Delegate:

While that looks legal it actually results in the compile time error “An anonymous method expression cannot be converted to an expression tree.”

There is a lot of complexity in parsing the AST, far beyond the scope of this article. However, the MSDN does have a nice diagram that helps explain how the following slightly more complicated Lambda expression
that determines if a string has more letters than a number:

So LINQ to SQL uses this Expression Tree technique to parse a plethora of possible code that you could throw at it and turn it into smart SQL. For instance check out a couple of the following conversions that LINQ to SQL will (or will not) perform:

This should only pick up Beverages that have fewer than 5 items in stock
regardless of whether they are discontinued and any other products that aren’t
discontinued. Would you believe that it runs a single SQL statement:

Wow, it sure isn’t pretty, but it scales to multiple conditionals, and most
importantly it didn’t return all products and process them in memory. Not bad.

Conclusion

I asserted up front that using expression trees and the strong typing that comes with them is the reason LINQ to SQL is inherently better that NHibernate. I really can’t make that claim without admitting one of LINQ to SQL’s biggest shortcomings: It currently does not support multiple table inheritance. Ultimately, however, it’s a short term fault since the forthcoming LINQ to Entities does. And I stand by my claim because from a long term perspective as long as technologies like NHibernate remain pure ports of Java code they will never realize the full benefits of
equivelant LINQ technologies that take advantage of .Net's native strengths: like expression trees.

To say that Linq to SQL is better than NHibernate is misleading. To say that LinqToSql is better than NHibernate's query engine may be fair, but there are many other benefits that NHibernate has over LinqToSql.

Combine those with the LinqToNHibernate project, or the Rhino.Commons additions and you're onto a type-safe winner.

We at EntitySpaces are pretty on par with LINQ as far as our DynamicQuery API goes, check it out... I'm not trying to spam, just showing you that LINQ is not the only way, or even the first, nor does it go half the places where we do.

We at EntitySpaces are pretty on par with LINQ as far as our DynamicQuery API goes, check it out... I'm not trying to spam, just showing you that LINQ is not the only way, or even the first, nor does it go half the places where we do.

I'm essentially an ms guy, using.net since pre-release. For the last year or so I've been using java as I wanted to add some more strings to my bow. The java projects I have worked on tend to use spring and hibernate and for java it works really well. Spring has a HibernateDaoSupport class which gives you basic crud methods out of the box. No need to write anything sqlesque, whether strongly typed or not. Sure, you have to dive into hql or sql if you have anything complex or unusual to do.

Anyways, I felt with 3.5 it was time to check out linq and I'm porting one of my java apps to c#. There is an inheritance hierachy which uses multiple tables. Its a reasonable use of inheritance. Multiple tables is the best way to model it in the database. Using linq I cant do this. Single table inheritance is not the way to model this sort of inheritance and feels like a fudge. I am a great fan of .net, but if ms are going to go to the bother of integrating query functionality into the language they need to get this addressed. And throw in crud for single entites and collections out of the box too. I'm thinking of using nHibernate instead now.