Executive Summary: Use a formula in an NHibernate mapping to facilitate searching the entire string, “LastName, FirstName”, for a User object.

Will see how long this Executive Summary thing lasts. Getting tired of people wasting my time by posting comments saying I’m wasting their time. (I’m also working on an idea for curbing “Smells like fail” comments as well but it’ll involve some serious changes to your browser. Or, based on the average age of people that say things like that, a call to your parents to discuss how much time you spend on Facebook.)

When I’m not Google Web Toolkittin’, I have a nice side project that I use to keep my .NET skills sharp, keep one foot in the door, and whatever other reason I can think of to avoid saying the real reason, which is “pay the bills”. Because one thing start-ups ain’t got a lot of is stable (read: any) income.

In said project, I have a page with an auto-suggest feature to search for users. I.e. you enter some text, and it finds any users with the entered text in the name and displays them in a dropdown. I’d show a screenshot but in the time between when I developed it and when I wrote this, the feature was dropped.

The mechanics of the auto-suggest might be the subject of another post but I doubt it because it’s been covered to death (though not so much in ExtJS which is what we’re using). I’m going to talk about what happens in the back-end. That is, how do I get the data from the database with NHibernate.

We’re using Linq to NHibernate so my first pass was straight-forward:

public IList<User> Search(string searchText) {
    var session = NHibernateSession.Current;

    return ( from w in session.Linq<User>()
                where w.FirstName.Contains(searchText) || w.LastName.Contains(searchText)
                select w).ToList();
}

This works exactly as one would expect. If the user enters "will", it will display "William F. Buckley" and "Ted Williams" and "William 'Wild Bill' W. Williamson" in the search results. Or rather, it will show "Buckley, William F.", "Williams, Ted", and "Williamson, William 'Wild Bill' W." because that's how we're displaying our search results.

And to facilitate that display, we have a Name property on the User object:

public string Name {
   get { return LastName + ", " + FirstName; }
}

Problem is that this search doesn't cover a common scenario. What if the user types 'Williams, T'? This would be a natural thing to do. They want Ted Williams, so they start typing Williams. The search results are too big and they are showing items in the "Last, First" format so it makes sense to keep typing and try to narrow it down further.

The code above will return zero results for such a search. Really what we want is to search the Name property, like so:

public IList<User> Search(string searchText) {
    var session = NHibernateSession.Current;

    return ( from w in session.Linq<User>()
                where w.Name.Contains(searchText)
                select w).ToList();
}

Which doesn't work either because Name isn't a database field and as yet, NHibernate is not able to parse formulas in your properties and convert them into SQL or Criterion.

But NHibernate *does* allow formulas if you describe the formula to it in the mapping. We're using Fluent NHibernate (assuming it hasn't been merged into the NHibernate project yet and completely replaced mapping files, which it should be):

public class UserMapOverride : IAutoMappingOverride<User>
{
    public void Override(AutoMapping<User> mapping)
    {
        mapping.Map(x => x.Name).Formula("LastName + ', ' + FirstName");
    }
}

And update the Name property in the User object accordingly: 

public virtual string Name { get; private set; }

Now, our Search function works the way I want.

Kyle the Formulaic