OR-ing criteria with NHibernate, or “How to include it all”

Gonna delve away from CodeBetter tradition into more of the realm of our sister site, Devlicious. I.e. This post will be about code, not about thinking about writing code. Having gone through this monstrous exercise though, I gotta say, Ivory Tower Architect is looking like a mighty fine role these days.

I’m working on a search interface that uses NHibernate to search for people using various criteria, such as Languages Spoken (generally a zero to many relation), Special Skillz, and Preferred Side Dish for Various Roadkill. It uses HBM files and a couple of the collections on the Person object are mapped as composite-elements, like so:

This is just a representative sample. The Person class has a collection of Skill objects (accessed via a property named SpecialSkills) as well as a collection of PersonalHygiene objects (mapped via the PersonalHygieneMilestonesReached property). The PersonalHygiene object contains a HygieneAction object (which is just an ID and Description) and a DateLastPerformed property.

In the search interface, we’d like to search for people who have, say, bathed. (I’m ignoring the date last performed to avoid muddying the wa–err…clouding the issu—ummm, complicating things.) So the search interface gives us a dropdown list, the user selects “bathed”, and our NHibernate-based data access class goes on its merry way.

All well and good and does what we want. It’s slightly different than searching on a property of the Person object in that we are searching for the existing of an item in one of its collections. Hence, the use of CreateAlias.

The next task: Add the ability to apply multiple filters to our list of people. If someone matches ANY of the supplied criteria, they are returned by the search. That is, we want to OR the criteria together.

Let’s search for anyone that has bathed OR has the ConversesAtAThirdGradeLevel special skill. Luckily, there is an Expression.Or method that, in theory, should make this easy. Let’s take a look at this special case before generalizing some.

This is essentially a combination of the two queries. We need to add all the aliases up front because Expression.Or takes two ICriterion objects, not two ICriteria objects. This can be generalized as well by modifying the FindByXXX methods so that they look more like this:

Kinda messy with all the if statements and the specialized AddAliasAndGetCriteriaForXXX methods but those can be cleaned up. I switched to using a Disjunction because it allows you to chain a bunch of ICriterion objects together, rather than limiting it to two like Expression.Or does.

The reason I won’t show you the cleaned up code because when all is said and done, this won’t actually work. If you look at the underlying query being executed, it looks something like this:

On the surface, this looked correct to me intuitively. It gave me results and those people had the specified skill and/or hygiene habit.

But I noticed there were people missing in the results. Specifically, people that had either no special skills or no personal hygiene habits. Which is when the INNER JOIN assignment from my second year databases class came rushing back to me. Of course, if you use INNER JOIN, you exclude any data where there is no match between the two tables. So my little SQL statement above will include only people who have BOTH a special skill AND personal hygiene and only those that match the given criteria.

Instead, I could use OUTER JOIN. But I didn’t much like how things were starting to meld together with the aliases and the criteria so I decided on a separate approach. In essence, for each search filter, I want to run a query. Then I want to return the list of people from all those queries. I could do that programmatically, I suppose. I.e. Execute the queries, then use List.Intersect to pull them all together.

But that ain’t how the hillbilly rolls. Instead I went with subqueries. The underlying pseudo-SQL:

SELECT *
FROM Person
WHERE Id IN ( SELECT PersonId FROM Person_SpecialSkills WHERE SkillId = @p0 )
OR Id IN ( SELECT PersonId FROM Person_PersonalHygiene WHERE PersonalHygieneId = @p1 )

The changes: return the actual query and specify a projection, which is fancy-Hibernate-speak for "what fields to I want this query to return?".

From here, I created the main search method:

In this case, finders is a dictionary of Func<int, DetachedCriteria> objects keyed on a SearchType enum. It’s initialized elsewhere. I use it as a sort of pseudo-strategy-type thing and if you claim that it’s not *really* a strategy pattern, I won’t disagree because I don’t care. I just know it’s cleaner than using a switch statement.