Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

On diving into NHibernate

Here’s the thing with NHibernate: It’s hard. I’m mildly, bordering on moderately familiar with it and while it’s a powerful product, it’s a complicated one. This is due in no small part to the fragmented nature of the documentation. Some is on the official site, some in discussion groups, some in FAQs, and some in books. It’s getting better but it is still a very daunting and frustrating task to do seemingly simple things when you aren’t a master.

We’ll look at the example that you knew was coming: Select all Jobs that have at least one Location with a Section = 15. Here is the SQL:

SELECT		*
FROM		Job
WHERE 		JobID IN
	( SELECT 	JobID
	  FROM		Location
	  WHERE 	Section = 15 )

Not too bad thanks to years of SQL that have been ingrained at the expense of remembering vital statistics and, on occasion, basic motor skills.

Now before I post a solution, I should point out that one of the reasons NHibernate is hard to figure out is that the domain does not lend itself well to Google. Take this example. I want to convert this SQL to ICriteria format. Searches for various combinations of NHibernate, Subqueries, Collections, and Children do not make one reach for the "I’m feeling lucky" link. I did find this somewhat helpful link until I discovered that the whole thing could be replaced with an Expression.IsNotEmpty criteria.

I’ll show *a* solution to my problem:

    var subquery = DetachedCriteria.For<job>( );
    subquery
        .SetProjection( Projections.Property( "Id" ) )
        .CreateCriteria( "Locations" )
            .Add( Property.ForName( "Section" ).Eq( "15" ) );

    var query = DetachedCriteria.For<Job>( );
    query
        .Add( Subqueries.PropertyIn( "Id", subquery ) );

I’m going to venture an opinion that this is *not* an intuitive piece of code when, in the back of your mind, you’re trying to map this to what you know of SQL.

Here is what is going on as I see it. The subquery is equivalent to the following:

SELECT      JobID
FROM        Job j
INNER JOIN  Location l
ON          j.JobID = l.JobID
WHERE       l.Section = 15

This is different than the subquery above and there is a reason for that, which I think I’ll get to. But first, here are some specific areas of confusion:

  • SetProjection is essentially saying "what fields do you want to return?" If you ignore it, it will return whatever object the query is for (in this case, a list of Jobs).
  • CreateCriteria( "Locations" ) is used for the INNER JOIN on Job. The Add will create a criteria for the Location (which is why I indented it further).

The reason for my confusion is the disconnect between the terms when mapping them to what I know of SQL. Could be my cursory knowledge of the underlying theory. Could be that it’s just hard coming up with names for these things.

Now we get to the reason why the subquery is on Job and not Location. In my domain model, Job contains a collection of Location objects. The Location objects do not have a reference back to the parent, nor do they have a JobId property. There is a JobId column for the table in the database but that’s used only by NHibernate to populate the collection of Locations for a particular Job.

If I were to create the subquery on Location, I have nothing to return in the Projection. There is no JobID property on the Location. And NHibernate deals with objects in its queries (mostly), not database schema. So I needed to create an INNER JOIN subquery solely so that I could return the ID property on a Job object.

Once that is finished, we create the main query, again on Job. The sole point of confusion I had here was that I saw In in the list of methods on Subqueries and tried to use that instead of PropertyIn.

It took an inordinate amount of time to get to this point compared to how much time I spend with other tools. Like I said, maybe it’s the problem domain. Maybe it’s the way my brain is wired for this type of problem.

The underlying point is…well, I’m not all too sure actually. Not sure what the recommendation is here. I’m not knocking NHibernate because the fact is, it does make my life easier for the 90% of the time I’m not doing stuff like this. The point isn’t really to stick with it either, though again, I think you should if you’ve tried it and dismissed it for being too complicated. Guess I just wanted to say I worked really hard today.

Concluding note: I’m aware of the existence of HQL. I’m building a search screen and the prospect of converting an arbitrary number of search parameters into HQL didn’t appeal to me.

Kyle the Unrelenting

This entry was posted in NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://www.castleproject.org Ted Gifford

    You can also use a (not) exists query. This query finds months that haven’t received a MonthlyUpdate for a given government.

    DetachedCriteria criteria = DetachedCriteria.For(“td”)
    .AddOrder(Order.Desc(“StartTime”)) // Most recent month first
    .Add(Expression.Eq(“TimeType”, TimeType.Month)) // Just months
    .Add(Expression.Lt(“StartTime”, DateTime.Now)) // but not future months
    .Add(Subqueries.NotExists(DetachedCriteria.For(“mu”) // only months without an update
    .SetProjection(Projections.Constant(1)) // (NHibernate requires a projection for sub-query, apparently)
    .CreateAlias(“TimeDim”, “innerTd”)
    .Add(Expression.EqProperty(“innerTd.id”, “td.id”))
    .Add(Expression.Eq(“Government”, Government)) // for this government
    ));

    Pardon the over-engineered data model. A MonthlyUpdate has references to a TimeDim and Government. TimeDim contains specific instances of Year, Month, etc. (e.g. 2008 calendar year, or June 2008).

  • Kyle Baley

    @Ayende: Damn, you and your logic. I saw that way of doing things a while ago and dismissed it because my Location didn’t have a reference to its parent Job. Not realizing that I could change that…

  • http://www.ayende.com/Blog/ Ayende Rahien

    Kyle,
    The code is trying to tell you something. You have a problem with the model if you have to have awkward queries there.
    Add a Job property to Location, in which case you could do:
    var subquery = DetachedCriteria.For().SetProjection(Projection.Property(“Job.id”)).Add(Expression.Eq(“Section”, 15));

  • http://weblogs.asp.net/bsimser Bil Simser

    I think it’s a general issue for everything. Even in MS land, there isn’t a single place to go for the best information. For me, the best information is either in my head (if I know it) or Google. Maybe that in itself is a sad state of the union from a social commentary.

    And yes, I take full blame for leading you into the NHibernate path through a layer of abstractions. My bad. Had we been able to keep you around longer (my choice was to but powers that be overrode that) we would have got more to the heart of building out those abstractions.

    Until next time we get to work together, we’ll just keep the fires burning with blogs and comments and emails my friend.

  • Kyle Baley

    @Bil
    You could be right. But it *feels* harder to me. And that might be because I’ve been working with it on a cursory level for quite a while now and I feel like I should be able to work things like this out more easily by now. It wasn’t like dealing with Rhino Mocks or IoC containers where much of the hard part of the learning curve was up front.

    That’s partially your fault by the way since my first exposure to NHibernate was on our project that already had it implemented and abstracted. For me to deal with it, I just needed to copy and paste an existing mapping file and muddle my way through modifying it for a new class. Everything else just worked.

    Now, with me starting from scratch and having to build the infrastructure myself, I’m feeling like I should have these things figured out. It doesn’t help that I’m working solo without anyone to bounce ideas off of, at least not in real-time.

    With all that said, I was able to get reasonably up-to-speed with LinQ and lambdas comparatively quickly. I’m no expert by any stretch but I feel like I can muddle through them more easily, probably because help is a little easier to come by.

    And I don’t want to imply there should be a centralized “Click here” site for NHibernate. But I find that Googling for problems with Linq and lambdas (and even things like Rhino Mocks and Monorail) is much easier than for NHibernate. I don’t profess to know why that is or if it’s a general problem or just the way I think. Just noting it as an issue.

  • http://weblogs.asp.net/bsimser Bil Simser

    I have to respectfully disagree. NHibernate isn’t hard any more than software development isn’t hard. It’s new. If you haven’t used an ORM before then you’re in for a bit of learning curve. Much like picking up a new language, new tool, or new technology. Saying NHibernate is hard is like saying everything is hard (.NET, 3.5, lamda, LINQ, you name it).

    I agree that the NH community is all over the place. Ask 3 developers using NH and get 5 different answers. Answers are scattered all over the place as you say. Again, this is nothing new to the NH community or the open source community. It’s just how it is.

    Should every community have a “Go Here” site where all your questions will be answered? I challenge you to show me that for anything, programming or non-programming related. It’s just how humans diverse information. Everyone learns differently. Some from code samples, some from pairing, some from reading books, some from personal spikes and exploration. I don’t think NH or any tool is different here nor are we, as a community going to ever change it. And why would we?

    It may not be optimal, but that’s part of the fun. If we wanted canned systems that always were done the same way there would be little room for creativity. For example how many different implementations of the Repository (DDD) pattern have you built or seen? Each time they’re all different. If Eric Evans came out with a snippet of code saying “use this code whenever you built a repository” development would be boring. It’s through challenges and learning that we extend and explore. It’s through these exercises that we discover and push the envelope, opening doors to new ideas. Without that exploration we’re all just mindless robots pushing bits.

    I for one welcome our NH overlords and the times that are upon us.

  • Kyle Baley

    Reading the comments, I think I found a couple of points to the post. One was to get some sense that I was on the right track, which is often the underlying theme to most of my posts even if I don’t outright say it. Thanks to all for the encouragement.

    But another is: how are we to encourage others to use a product like NHibernate when it is not only complicated but open source (which scares many companies, sometimes justifiably so)? Yes, it helps make things easier but many people will take it up just begging for an excuse to dump it at the first sign of trouble. And not everyone has the time luxury I currently do of being able to push through the learning curve.

    Like Doug, I’m encouraged by NHibernate.Linq even if this post was the result of my dumping it in favour of a more “traditional” NHibernate approach. It’s simply not ready yet but at the rate it’s being developed, it will get there pretty quick. When that happens, here’s hoping it becomes more widely adopted.

  • http://blog.symbiotic-development.com Symon Rottem

    I think you’re taking the right approach; the criteria queries are ideal for the search screen type of problem you’re attaching – dynamically generating HQL is hard work and fragile.

    The ICriteria interface does take some getting used to – it’s more difficult than HQL for the same reason programming is more difficult than writing a sentence; it’s not a natural way of thinking about a problem. After doing it for a while it does become easier, just like writing code. :)

  • Edgar Zavala

    You always can use HQL and Native SQL, not always the best solution, declarative quering is good. But some times is better to create a native sql statement or an hql to do so (specialy in high optimized queries). I use NHibernate a lot (i use it in Java from some time ago and in .Net since it was ported) and is an excelent ORM (for me is one of the best tools from Java ported to .Net), but some times … just some times… is more complex to wath you expect on an ORM

    Just my $0.02

  • http://www.codesqueeze.com Max Pool

    You’re just going through the same crappy learning curve everyone else on NH has..just like TDD, you will push through it and be glad you did.

  • Doug Mayer

    I agree that NHibernate is very difficult to learn and the fractioned documentation doesn’t help (though those guys have been doing a great job on the blog you linked). The model you’re required to work with is what makes it particularly difficult I think… it’s just not intuitive for someone who’s not intimately familiar with NHibernate. While NHibernate.Linq is still in its infancy, I think we may be able to look to that it to bring the familiar LINQ syntax to (most of) our queries.

    May I also say your blog is a particularly easy read… just the right amount of funnies to keep us entertained and still take away something.