...rediscover the joy of coding

LINQ-to-Entities: The Blackberry Storm of ORMs?

(In case you missed the reference, the new Blackberry Storm has been widely slammed as an inferior copy of the iPhone, and was released basically unfinished to try to fend off its better rival).

Note: the first part of this article is a bit of an opinionated rant - if you want to skip to the actual code examples a little further down and make up your own mind, that's fine by me.

A Bit of History

One of the best new features to appear in .NET recently has been LINQ (language integrated query) - and particularly, LINQ-to-SQL. This marvellous bit of the framework lets you define a mapping between database and objects (or more likely, automatically generate the mapping from a database schema), and then query and update the database in a type-safe manner, using syntax that nicely bridges the gap between C# and SQL.

Unfortunately, LINQ-to-SQL was a bit of a skunkworks project - while hyped projects such as ObjectSpaces and WinFS collapsed under their own weight, LINQ to SQL quietly delivered.

Clearly for political reasons this successful ORM project had to be stopped, and fast. (Nice article here).

So enter LINQ-to-Entities - the ADO.NET team's "answer" to LINQ-to-SQL. Unfortunately, while LINQ-to-SQL was a wonderful, polished jewel of functionality that was a nice example of IJW ("it just worked"), LINQ-to-Entities seems to be a product of Architecture Astronauts who would rather bamboozle us with their cleverness than actually provide useful solutions.

Enough Ranting - Some Examples

I've written a small handful of applications using LINQ-to-SQL, so when I found that LINQ-to-Entities was its replacement, I thought I'd start my next application experiment using it. I'm going to include the source for these examples in a little test application at the end of this article, if you'd like to try it for yourself. I've used identical code for both LINQ-to-SQL and LINQ-to-Entities, with minor changes for logging and creation of the entity model. Both schemas were imported from the same database.

The Schema

The schema contains just two tables, joined by a single foreign key relationship:

Problem #1 - Silently Gives Differing Results

This problem is to my mind the worst that I've found so far.

Suppose I search for a customer:
var alice = data.Customers.First( c => c.Name == "Alice" );
Fine, that works nicely. Now let's see if I can find one of her orders:
var order = ( from o in alice.Orders
              where o.Item == "Item_Name"
              select o ).FirstOrDefault();
LINQ-to-SQL will find the child row. LINQ-to-Entities will silently return nothing.

Now let's suppose I iterate through all orders in the database:
foreach( var order in data.Orders )
  Console.WriteLine( "Order: " + order.Item );
And now repeat my search:
var order = ( from o in alice.Orders
              where o.Item == "Item_Name"
              select o ).FirstOrDefault();
Wow! LINQ-to-Entities is suddenly telling me the child object exists, despite telling me earlier that it didn't!

Let me put that in bigger letters in case you missed the significance:
LINQ-to-Entities will return different results depending on what previous queries you've executed!
That's hardly a transparent mapping between database and objects.

Problem #2 - No Support for Constant-Folding

Consider the following code:
using( var data = new LinqToEntities() )
  var names = new[] { "Alice", "Bob", "Dummy" };

for( var i = 0; i < names.Length; ++i ) { var customers = from c in data.Customers where c.Name == names[i] select c;

// Do something with the customers found. } }
LINQ-to-SQL handles this fine, treating names[i] as a constant expression. LINQ-to-Entities fails with:

"The LINQ expression node type 'ArrayIndex' is not supported in LINQ to Entities."

Problem #3 - No support for Single() or SingleOrDefault()

LINQ-to-SQL supported a nice method of 'asserting' that you were expecting zero or one results - for example if querying against a key:
var customer = ( from c in data.Customers
                 where c.Name == "Alice"
                 select c ).SingleOrDefault();
LINQ-to-Entities however says:

"The method 'Single' is not supported by LINQ to Entities. Consider using the method 'First' instead."

But First() doesn't express what I want - namely that more than one result is a logical error.

Problem #4 - Exceptions

The final problem I've noticed is that LINQ-to-Entities throws and catches exceptions internally. Just running the test application with exceptions turned on throws multiple exceptions:
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll

Additional information: Could not find file 'C:\<snip>\bin\Debug\System.Data.Resources.CodeGenerationSchema.xsd'.
A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll

Additional information: Could not find file 'C:\<snip>\bin\Debug\System.Data.Resources.EntityStoreSchemaGenerator.xsd'.
I now have the choice between turning off exceptions (thus hampering my own debugging), or having to press 'continue' several times. I guess they hadn't heard of File.Exists().

Bonus #1 - Refresh From Database

OK, I've been a little mean to LINQ-to-Entities. Surely it has some good points?

From my perspective, there is just one feature that I like over LINQ-to-SQL. (I know it supports many-to-many mappings of tables and classes. Seriously, what tiny percentage of users would want to do that?). The designer in LINQ-to-Entities allows you to refresh your mappings from database changes, whereas LINQ-to-SQL forces you to either make the changes manually, or delete everything and re-import.

However, that's a designer feature. Couldn't we have had that for LINQ-to-SQL as well?

Source Code

If you'd like to try these examples out for yourself, I've included an ZIP file. You'll need Visual Studio 2008 SP1, and MS SQL Server 2005 or later.

The ZIP file contains a create database script you'll need to run, which creates a database called 'LinqTest' and populates it with a few rows of data.
Download "linqtest.zip", 14Kb.

In my opinion, we seem to have given up on a perfectly good, working ORM solution (LINQ-to-SQL), and replaced it with a buggy, half-baked one (LINQ-to-Entities). I sure hope v2 improves matters, because for now, switching to the entity framework is a downgrade.