NHibernate – ByATool

Fluent Nhibernate, Linq to NHibernate, MS Test, Fluent Assertions, and a working example

So out of pure curiosity I decided to go down the NHibernate path and it had nothing to do with the headache that is Entity Framework… nothing at all.

First I wanted to see where Linq to NHibernate was at after hearing about it years ago. Turns out it’s doing pretty well… and actually works thanks to this link. So with that in mind, I also wanted to check out Fluent NHibernate since I had somehow knew it was a project created with the hope to remove the config files NHibernate used. (No idea how I knew that, must have known someone who used it.) Well just so I could be completely wild, I just decided to try out Fluent Assertions which is a library used to help MS Test assertions to be readable. Crazy idea I know.

Well after a bit of reading and a couple hours of work, I came up with a working example of them all in one project. Go me.

Here it is.

Only thing you need to do is have a database handy and open up the “~\NhibernateTest\NhibernateTest\NHibernateHelper.cs” file and set the Username, Password, Catalogue, and Server name. Then just create a database (But not the tables) that corresponds to the values you entered. Now the nice thing is that the first time you run any of the tests, it (Being Fluent NHibernate) will create that tables and columns needed. This is something that works a lot like Active Records for Ruby. This behavior is controlled by:

.ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))

The second True is the important one. If you keep that True, it will drop and recreate the tables every time you run a test. Change it to False and it will stop doing that.

Just keep in mind I’m not presenting any Best Practices here. This is an example but not exactly production ready.

NHibernate Cross Join: The multi-part identifier … could not be bound..

Something else I found out about NHibernate, at least an older version, and cross joins… and if you’re reading this chances are you have the same version. Say you want to cross join something but there are also other joins. You would think it should be like this:

    SELECT
       user.UserName, status.StatusName
    FROM
       User user, UserStatus status
    LEFT JOIN
      user.Address address
    WHERE
      user.CreatedDate = status.CreatedDate

Ok so once again, this query is kind of stupid but the idea is there. With NHibernate you have to Cross Join when you need to match two objects but not on the mapped keys. Meaning that User and Status might be mapped on UserID ie Status.UserID = User.UserID. But what if you wanted to match them on something else, like a date. Well that’s where the cross join came in. The Sql might look like:

    SELECT
       user0_.UserName, status0_.StatusName
    FROM
       ByATool.User user0_, ByATool.UserStatus status0_
    LEFT JOIN
       ByATool.Address address0_ ON address0_.UserID = user0_.UserID
    WHERE
       user0_.CreatedDate = status0_CreatedDate

Looks fine right? Well it’s not according to SqlServer 2005. Turns out the cross join has to appear AFTER the other joins. So in reality it should look like:

    SELECT
       user0_.UserName
    FROM
       ByATool.User user0_, ByATool.UserStatus status0_
    LEFT JOIN
       ByATool.Address address0_ ON address0_.UserID = user0_.UserID,
       status0_.StatusName
    WHERE
       user0_.CreatedDate = status0_CreatedDate

Which means the hql looks like this:

    SELECT
       user.UserName
    FROM
       User user, UserStatus status
    LEFT JOIN
      user.Address address,
      status.StatusName
   WHERE
      user.CreatedDate = status.CreatedDate

See how the cross joined object is now after the Left Join? Looks odd but this is what it takes to get the cross join through. Kind of annoying.

NHibernate.QueryException: expecting ‘elements’ or ‘indices’ after…

Yes, it’s true. I have two lovers. Well more like I’m stuck with NHibernate due to some kind of stupid forced marriage and the other is my somewhat quirky but ultimately young and hot mistress Entity Framework. And now that I’ve admitted to such an atrocity you are about to run screaming, I’ll get on with what the hell this means.

So I ran across this the other day and well I’m sure a smart person would immediately understand what it means, but that smart person doesn’t write for this blog. You’re stuck with me. So after some staring at the screen and praying to whatever god I just learned about on wikipedia, I figured it out. Now I can’t promise this is the only reason to get this error, but this is the answer I have.

Say you have a user and with that user you have a list of events. Now it should be obvious that there is an event class, an event table, and user table. Simple enough. Now in hql, it would look something like this:

    SELECT
       user
    FROM
       User user
    LEFT JOIN
       user.Events event
    WHERE
       user.Events.Status = SomeStatusEnum.SomeValue

And yes I realize that it doesn’t need to be a left join, but just roll with me on this because I don’t feel like making a better example. So if you look close enough at the query you should see something wrong… Why would I join the objects and then refer to the event status through the user?

       user.Events.Status = SomeStatusEnum.SomeValue

Because I’m a tool. Yeah turns out that, to me, crytic message is really saying “Use the joined object alias you f-ing tool.” In other words:

       events.Status = SomeStatusEnum.SomeValue

And boom. No more error.