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'.
and
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.
Conclusion
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.
See this http://www.infoq.com/news/2008/12/EF-Large. It presents problems or issues with large data models
Saying “LINQ-to-Entities will return different results depending on what previous queries you’ve executed!” is a little harsh, the Entity Framework just handles lazy loading differently so that it does not bring your whole graph into memory.
If you call
alice.Orders.Load()
or perform the first query as
var alice = data.Customer.Include(”Orders”).First(c => c.Name == “Alice”);
alice.Orders will be filled and ready to use, when you iterated over data.Orders, it just happened to load alice.Orders in the process because it was loading them up anyway.
Hi
I’m a Java developer, but like to investigate technology from MS when I have time. While C# and Visual Studio are actually pretty nice nice, Asp.Net/Ado seem a few years behind the latest and greatest in the Java world.
Asp.Net MVC looks promising, and as far as a OR solution, might I recommend NHibernate [http://www.hibernate.org/343.html]. Together with Spring [http://www.springframework.net/], web application programming is advancing nicely.
Heya, I’m certainly not an expert, but I think for problems such as #2, the expression tree that is being generated for the deferred comparison operation is being flummoxed.
If you restructured the code to use Contains, which translates to a SQL “IN” query, you could achieve the same goal.
In problem #1, I believe you have to manually include the association in the query to have it pull back data, else you have to do a .Load() to get the data after the initial query.
I know the argument though. Why should the same operation that work with one fail in the other? Bothers me too.
I’m in a situation where I have a few projects using L2S, and I’m happy with it. EF looks interesting for a few different reasons, but none good enough to make me want to switch.
At PDC 2008 Tim Mallalieu did a session on the future of the Entity Framework.
http://channel9.msdn.com/pdc2008/TL20/
It’s worth a peek to see where they’re heading, and I think it’s in the right direction.
> 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.
Here is a tool that fixes that. Plus it sucks out your database comments and turns them into C# XML comments.
http://www.infoq.com/news/2008/12/Huagati
Here is the response from the ADO.NET team:
http://www.infoq.com/news/2008/12/Lazy-Loading
[...] Vote LINQ-to-Entities: The Blackberry Storm of ORMs? [...]
[...] Vote LINQ-to-Entities: The Blackberry Storm of ORMs? [...]
The strange thing that is happening with EF is not the lack of lazy loading, its that the data showed up magically after you queried for all orders. The reason it showed no orders to begin with was because there is no implicit lazy loading, so the collection is empty until you explicity call load. Then when you queried it again, after looking at all orders, suddenly there were orders loaded; the collection changed on its own. This happens because EF allows merge behaviors ala DataSet when queries are run. Any entities that are retrieved are put into the cache and if related entities are found also in the cache they are wired together. So, simply because you looked at the orders corresponding to your customer via a different query the customer’s order collection was populated. This is a surprising side-effect to say the least. Fortunately, I believe it is possible to set the merge options for the query so you can turn this off.
There’s another significant problem I ran into with the EF, which made it a dealbreaker for me. It refuses to expose foreign keys. They decided to be “holistic” (actual quote from MS!) and stick to the idea that this is fully object-oriented and not relational in the least. So therefore, code like this simple L2S code:
Product chai = db.Products.Where(p => p.ProductName == “Chai”).Single();
chai.CategoryID = 1;
db.SubmitChanges();
Will not work, if CategoryID is a foreign key into the category table. Instead, in order to set the category of an object, you must actually waste time with another database call to retrieve the category object, and then set it:
Product chai = db.Products.Where(p => p.ProductName == “Chai”).First();
Category beverages = db.Categories.Where(c => c.CategoryName == “Beverages”).First();
chai.Category = beverages;
Unbelievable.
There are many blogs out there which seem to show that the SQL queries that EF generates are remarkably slower than L2S queries as well. I haven’t tried this out for myself, but it wouldn’t surprise me. If MS decides that L2S is dead, we may just have to reverse engineer it and make it ourselves. EF is a complete joke.
I am currently tech-editing a book on the Entity Framework, and wrote one on LINQ to SQL, so I can feel your pain — because I’m feeling it too.
I was one of those, like many others, who thought the EF was going to be really, really revolutionary. While I always kinda understood that L2S was the “dime-bag” to get us all hooked on LINQ and therefore buy straight into the EF, what I have come to realize in the course of this current project is that for probably 90% of projects, the Entity Framework is the equivalent of watering a houseplant with a firehose. Just too much. In these cases, L2S is just the kind of lightweight, easy-to-handle framework you need.
To be fair, the lack of support for implicit deferred loading is not such a big deal. A lot of folks actually prefer the EF way; having gripes with the way L2S does it, saying that they don’t like the fact the L2S issues db calls behind the scenes — though I would answer that really, why should you *care* when the data gets fetched, as long as it does? But ultimately, implicit vs. explicit deferred loading is kind of a potato-potahto thing. There are good arguments for both. I, however, do agree that it’d be nice if they would just pick one direction and go with it.
I wholeheartedly agree with the rest of your points though. I suppose the only consolation is that Microsoft seems pretty committed to the EF and that it should improve over time. We shall see.
Actually the most current version of Linq-To-SQL doesn’t support .SingleOrDefault anymore…
So EF is consistent there…
There is another advantage of L2S over EF - in L2S, you can group by entity (”group by order.Customer”), in EF you cannot (you have to use “group by order.Customer.ID, order.Customer.Name, order.Customer…”, etc.)
Absolutely amazing that the Entity Framework fails on such simple stuff. I don’t want to know how bad it fails on the complicated stuff.
I see no reason why I should abandon NHibernate for the Entity Framework and I can’t see how Entity Framework will ever be able to catch up when I haven’t heard anyone (outside of the EF team) say anything good about it.
Imagine where we would be now if they EF team had instead spent their time improving NHibernate or LINQ to SQL instead of building EF?
@ Jon Kruger:
I don’t really know if I’d call these examples of the EF “failures”. Understanding that the EF was developed by the ADO team and L2S by the language team, it was probably inevitable that these inconsistencies would crop up.
What I think is the real shame here is that MS seems to want to “obsolete” L2S in favor of the EF. Why? Instead, why not bring both under the purview of the ADO team, cross-port the best concepts from both projects, iron out the inconsistencies with things like deferred loading, and keep both? I do believe the EF has its place, but as the article points out, L2S is an awesome, lightweight framework that is probably better suited to the majority of non enterprise-level projects.