Battle of the ORM’s - Querying

by DotNetNerd 8. December 2010 15:52

In the previous Battle of the ORM's post I looked at setting up and configuring NHibernate and Entity Framework. So the next step is to get down to business and look at querying - the most important part of an ORM.

Gimme gimme gimme

First I need to address that the Entity Framework CTP5 has been released. This means that some more features have been added, which you can read about on Scott Guthries blog. Besides that there are some classes that have been renamed, which actually makes my last post mildly obsolete already. The renaming means that the Database class now is called DbDatabase – apparently redundancy is the new black – and that the databaseinitializer classes are called CreateDatabaseIfNotExists, DropCreateDatabaseAlways and DropCreateDatabaseIfModelChanges.

These days most developers use LINQ, myself included, but a very common issue is that not all LINQ implementations are created equal. Actually the reason I started this whole comparison project, was because I have been using an NHibernate 2.x, where LINQ to NHibernate was a seperate project - and suffice to say I have had my share of fights with it. Now it has been "baked in", and it has taken a pretty big leap forward.

query

LINQ with Entity Framework

The most basic stuff you can do is to fetch a list of some type of entity. In EF this can be done like so:

var entities = new myProjectEntities();
IList<Company> result = entities.Companies.ToList();

So it is very straight forward, and without going over each LINQ extention method I will say that my experience has been that the implementation is very solid. The critique I have heard in previous versions has been that the SQL produced was less than optimal and that eager loading required passing in a string. Microsoft say that they have invested quite a bit in improving the SQL that is generated and in the very bits we can see that fetch now takes a lambda.

Before:
entities.Companies.Include("Employees").ToList();

Now:
entities.Companies.Include(c => c.Employees).ToList();

A lot can and is said about SQL generated by the ORM's. I won't go into it, because it is a huge topic filled with opinions and religious debate. I have to draw the line somewhere for this to not become a book, so this is one of those places.

LINQ with NHibernate

One of the things that are important to know a bit about when working with NHibernate is that managing the session object is pretty important. There are a number of strategies to do this; per request, per action and per business transaction just to name a few. This topic is not really suited for the topic of comparing the ORM's so I will just illustrate that opening the session and disposing it is something that needs to be handled. SO this makes the simplest scenario for fetching the compaines look like this:

sessionFactory = CreateSessionFactory();

using (var session = sessionFactory.OpenSession())
{
    IList<Company> result = session.Query<Employee>().ToList();
}

This is still pretty simple as it should be, since it is the most basic thing you can do. It is a little more noisy, but not much. The eager loading scenario is similar to that of Entity Framework, but it has had the option to use a lambda for some time.

IList<Company> result = session.Query<Employee>().Fetch(em => em.Company).ToList();

The bad news is that NHibernate does not handle it too well if you fetch more than one collection of elements for each entity – eg. a Comany with a collection of Employees and a collection of Cars. It has problems when mapping the resulting data to entities, so you get several instances of each Company back.

Just doing a bit of random query testing I still pretty quickly run into some issues such as SelectMany and Intersect operators not being implemented in NHibernate. I often use SelectMany, and eventhough you can do some of the same queries using joins it just feels more object oriented than having to think in terms of joins. In that sence Entity Framework seems to be more solid, and I feel more confident that what I write will actually work. To be fair Microsoft invented LINQ so it should not bee too big of a surprise that their implementation shines.

The abstraction of querying

Querying in both languages translate to a Domain Specific Language, that looks like SQL but adds a number of capabilities, so we can work with them as an abstraction of entities and not relational tabels. In NHibernate this is called Hibernate Query Language (HQL) and in Entity Framework it is called Entity SQL.

Besides HQL NHibernate also offers a stack of other API's for querying. The Criteria API, allows you to build queries as a number of criteria which can be detached allowing part of the query to be constructed elsewhere. QueryOver is an API which is wrapper on top of the criteria API that makes better use of strong-typing and lambda expressions - so it looks more like LINQ. A simple example to illustrate this is using restrictions like you see here:

var restriction = Restrictions.On<Company>(c => c.Name).IsLike("%soft%");
var result = session.QueryOver<Company>().Where(restriction).List();

The Criteria and QueryOver API's are very nice when you eg. have a search senario where the query is built by the user. These API's are pretty comprehensive, and a pretty unique feature for NHibernate.

Named queries give you a way to save queries in XML instead of having it inline - which is meant to be kind of like using stored procedures. Again putting code into XML makes me cringe, but of course it is better if the alternative is inline duplicated code.

MultiQuery, MultiCriteria and Futures are three API's that give you a way to group queries so they are executed as a batch. With MultiQuery and MultiCriteria you instantiate a list of queries and execute them at once. Futures abstracts away maintaining list and makes it defer the query until the data is accessed.

This is where we see the difference in how long the two frameworks have been around. You do not get anything like this for Entity Framework (yet?) so NHibernate is a lot more feature rich in that sence. The flip side of the coin is that some of the features pretty redundant, with Criteria, MultiQuery and MultiCriteria mostly being replaced by QueryOver and Futures. Never the less it is nice to have these features, even though you will probably use
LINQ in 95% of the cases.

Performance

As part of my tests I have looked at how the ORM's perform. So I created a database with sime simple objects, first with two properties and then scaled it to 5.
I filled it with 240.000 objects, and timed how long it took to pull the objects from the database. I had actually expected some difference in the time it took to map the objects, but it was not conclusive. Only thing I learned was that going from 2 to 5 fields showed that it scales very linearly in both cases.

I expanded the test to also include querying the database with a simple datareader, and while this did yield better results the difference was really quite small.
The average over 5 tries was that NHibernate took 36,7 seconds, Entity Framework 34,9 seconds and the reader 32 seconds.
So comparing the ORM's had a 5% difference which is hardly statistically significant when some cases had NHibernate as the fastest.
The 11% gain comparing the average of the ORM's and the reader, does show that it is a little faster though.

Conclusion

Looking at simplicity and on the foundation, that I reguard LINQ to be these days I think the Entity Framework is very well implemented. For me not having SelectMany with NHibernate is a neusence, so I will say that in this department Entity Framework wins out. When it comes to more complex scenarios where you need to build queries based on user input and you need batching to get better performance NHibernate comes out as the winner. You can absolutely solve these things with Entity Framework, but you do not get much help from the framework.

So which is “better” is an exercise for the reader, or in reality it is probably more a question of what you are building, what features you need and if you are into Microsoft or Open Source.

Who am I?

My name is Christian Holm Diget, and I work as an independent consultant, in Denmark, where I write code, give advice on architecture and help with training. On the side I get to do a bit of speaking and help with miscellaneous community events.

Some of my primary focus areas are code quality, programming languages and using new technologies to provide value.

Microsoft Certified Professional Developer

Microsoft Most Valuable Professional

Month List

bedava tv izle