Using an NHibernate Formula to aid searching

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

This entry was posted in Linq, NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Kyle Baley


    Yes, that’s true. It was more of a comment against HQL in general. But the argument has gotten weaker and weaker in my head over the years.

  • David

    “I also considered HQL but I’ve never liked the magic stringness of it. ”
    Aren’t there still magic strings in the mapping override?
    Just curious because I’ve seen this sort of comment quite a few times (from various folk).

  • NC

    Got some time today to test out latest build of Fluent/NH from Hornget, so i could test out NH3.

    It seems to generate the query but it throws an exception. Possible bug but I don’t know where to submit NH bugs/issues.

  • NC

    If you were to curb my comments how can I post as James? (tho my name isn’t James, it was just a random name I picked).

    Anyway after posting that I gave it a test, using Ayende’s LINQ to NHibernate, it doesn’t work :( not sure if it will with in NH3.0 because I haven’t had a chance to play around with it.

    It does work in L2S / EF however.

    This is from L2S:

    exec sp_executesql N’SELECT [t1].[UserId], [t1].[FirstName], [t1].[LastName], [t1].[Username], [t1].[Password], [t1].[SomeXML]
    FROM (
    SELECT [t0].[UserId], [t0].[FirstName], [t0].[LastName], [t0].[Username], [t0].[Password], [t0].[SomeXML], ([t0].[FirstName] + @p0) + [t0].[LastName] AS [value]
    FROM [dbo].[tbUser] AS [t0]
    ) AS [t1]
    WHERE [t1].[value] LIKE @p1′,N’@p0 nvarchar(2),@p1 nvarchar(5)’,@p0=N’, ‘,@p1=N’%lip%’

    Tho I perfer your Formula way, since if you had a property which joined the fields to return ‘FullName’, and you were passing expressions, you have to know before-hand that the FullName doesn’t exist.


    public IList Search(Expression> searchCriteria) {
    var session = NHibernateSession.Current;

    return session.Linq().Where(searchCriteria).ToList();

    var result = Search(x=>x.FullName.Contains(‘banana’));
    ^this would fail since FullName doesn’t exist in the mapping or db.

  • Kyle Baley

    See, now that’s why I always use a “here’s what I did” tone rather than a “here’s what you should do” one in my blog. I had no idea “let” existed (or “into” which I discovered while looking into this). James is an honorary Hillbilly for a day for making me learn stuff.

    That said, I also like the idea of using my existing Name property to perform the search rather than having to redefine the formula in the query. That way, I don’t have to change anything if we decide to change to a “First Last” format.

  • James

    Can’t you do something like:

    return ( from w in session.Linq()
    let name = w.Lastname + “, ” + w.Firstname
    where name.Contains(searchText)
    select w).ToList();

  • Moutasem al-awa

    I kindda like it, its clean and make your life easy .. thanks for sharing

  • Kyle Baley

    I tried that exact code but left it out of the narrative. It doesn’t work but I don’t remember the exact error. I also considered HQL but I’ve never liked the magic stringness of it. Truth be told, I’ve always used the criteria API or LINQ. Never had the need to use HQL.

    The query-only properties was new to me so thanks for that. In any case, we still need the Name property for other reasons anyway.

  • Tuna Toksoz

    Perhaps NH Search would fit better, but yeah, would be an overkill. On the other hand, you may fall back to HQL and remove that Name property completely (with query only properties ayende. com/ Blog/ archive/ 2009/ 06/ 10/ nhibernate- ndash- query- only- properties. aspx).

    I also wonder, can’t you do that in linq part?

    return ( from w in session.Linq()
    where (w.Lastname+”, +w.FirstnameName).Contains(searchText)
    select w).ToList();

    Also it coame to my attention: you’re probably using the old provider.