Phillip Haydon

Over the weekend I came across a blog post about NHibernate, and an apparent work-around for a feature not supported by NHibernate.

The original post can be found here: nHibernate LINQ workaround for System.NotSupportedException

Basically the author wanted to write something along the lines of:

Where the query joins to a list of Ids to filter the results out. This however, happens to throw an exception:

Normally this sort of query would be written like so:

This generates an ‘in’ clause in the SQL.

Nice and clean, pretty standard SQL.

Now I must admit when I first read his code I made the assumption that it would actually pull ALL results into memory, and do the join in memory.

I commented on his blog with the following comment:

I don”t see how this is a ”work-around” your first attempt at the query is attempting to join something in memory, to something still in the database. Thats clearly not, and will never be a supported feature of NHibernate… The second query is passing a list of ID”s into the database to use for comparison.

At the time of writing the comment, I was a bit annoyed. The author replied:

Never be supported in NHi”bernate… well it works using Entity Framework and that”s why I consider it to be a workaround.

So lets give it a go.

Give the following query:

The exact same as the attempt in NHibernate, it generates the following SQL…

WTF.

It creates a select statement, for each value that you want to select… I thought “oh ok, i wonder what the execution plan looks like for that, compared to a normal in-clause from NHibernate.

Using both the queries already shown above, showing the actual execution plans, I get the following:

Wow… But it gets better, I have 20 records in my database, and I thought, what happens if my list of Ids contains 10 different ids:

Same queries, but with:

Entity Framework (I don’t even want to paste this in here…):

NHibernate:

Now if you run with the execution plans, (I pressed execute 10 times before taking this screenshot)

The more parameters you add, the slower it gets.

You can’t blame Entity Framework though. EF supports the same query as NHibernate!

Entity Framework generates the following query:

So I personally think the original approach, to do a join on an in-memory list, is bad practice, regardless of weather it works or not. It’s bad.

It’s also bad to blindly write LINQ queries without ever looking at the SQL they produce. Sure it’s great to not have to worry about writing SQL, but you should still always be aware of the SQL that is been run, sometimes slightly changing the LINQ can massively alter the SQL that is produced.

A while ago MindScape posted a new feature called Ninja Properties for their ORM, LightSpeed. Their post happened to be a scenario I used to solve with LINQ 2 SQL using the ‘let’ keyword to create a composite type to query against.

This would allow you to do something along the lines of:

This nifty query would generate SQL like so:

After commenting in their blog post about it they implemented it and put it into the nightly build. So after some months I finally got around to testing it, for a couple of reasons. Back when NHibernate 3.0 was in Alpha, this technique didn’t work! So I thought I would test it out and see if it does, as well as checkout the SQL that’s generated.

I’m using the following ORM’s

• LightSpeed 3.11 Nightly Build (3.1 it doesn’t work)
• NHibernate 3.0
• Linq 2 Sql
• Entity Framework 4

The data model is simple, but I have two scenarios I want to test. I created a Member and Task. The Member has a One-to-Many with Tasks.The test data:
insert into member (firstname, lastname) values ('Robert', 'Williams')
insert into member (firstname, lastname) values ('Michael', 'Jones')
insert into member (firstname, lastname) values ('William', 'Brown')
insert into member (firstname, lastname) values ('David', 'Davis')
insert into member (firstname, lastname) values ('Richard', 'Miller')
insert into member (firstname, lastname) values ('Charles', 'Wilson')

insert into task (MemberId, Name) values(1, 'Task 1')
insert into task (MemberId, Name) values(1, 'Task 2')
insert into task (MemberId, Name) values(1, 'Task 3')
insert into task (MemberId, Name) values(1, 'Task 4')
insert into task (MemberId, Name) values(2, 'Task 1')
insert into task (MemberId, Name) values(2, 'Task 2')
insert into task (MemberId, Name) values(2, 'Task 3')
insert into task (MemberId, Name) values(3, 'Task 4')

Scenario 1: Find all Members where fullname contains ‘w’, case insensitive (lowercase the fullname)

Scenario 2: Find all Members who have more than 2 tasks.

These tests are not to say one ORM is better than another, I did this purely out of curiosity and am sharing my results.


 

Scenario 1:

I ran the same query against each ORM (to see the full script click here), the query is:

var poo = from s in ctx.Members
            let fullname = s.FirstName + " " + s.LastName
            where fullname.ToLower().Contains("w")
            select s;

All queries are identical with the exception of the ‘in’ part, varying between ORMs.

LightSpeed : from s in uow.Members
NHibernate 3.0 : from s in session.Query<NHibernate.Member>()
Linq 2 Sql : from s in ctx.Members
Entity Framework 4: from s in mmc.Members

The output was as follows:

All ORM’s worked perfectly. How about the SQL Generated from these?

Linq 2 SQL:

LightSpeed:

NHibernate:

Entity Framework 4.0:

Note: The first 3 queries are RPC’s and show in SQL Profiler with parameters, I’ve stripped the parameters out to show the readable query.

Great, so all 3 work fine. Tho I don’t like the SQL generated by Linq 2 Sql. Hard to believe Stack Overflow runs off it.

Scenario 2

The second scenario is again, the exact same query, you could write this multiple ways, however I found it cleaner to use ‘let’ back when I was using Linq 2 Sql. (to see the full script click here), the query is:

Again, the only difference is the ‘in’ part of the query.

The output was as follows:

All achieved the same results, except for LightSpeed. However I didn’t think NHibernate would support this?!? Seems they have done quite a bit of work implementing LINQ since Alpha.

How about the SQL generated?

Linq 2 Sql:

NHibernate:

Entity Framework:

I get the feeling that NHibernate generates more efficient queries than L2S/EF. Might be worth doing some performance testing against them oneday.

Anyway as you can see, ‘let’ keyword allows you to create some nice queries that are easy to ready instead of trying to complicate things in your ‘where’ clause, when writing LINQ.