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");
    }
}

public virtual string Name { get; private set; }

Now, our Search function works the way I want.

Kyle the Formulaic

Code challenge time! And by code challenge, I mean, "Do my work for me so I don't have to think/Google".

We have a parent object, called Parent, and each one has a collection of child objects, called AllegedChild. Ignore potential cycles for this exercise. The database has a single table with fields: ParentID, ParentName, ChildID, ChildName. The data is such:

1, Coding Hillbilly, 1, Brandi-Lynn
1, Coding Hillbilly, 2, Sammy-Jo
1, Coding Hillbilly, 3, Sammy-Jo Jr.
1, Coding Hillbilly, 4, Sammy-Jo Sr.
2, Donald Belcham, 5, Justice Gray
2, Donald Belcham, 5, Dave Laribee
2, Donald Belcham, 5, Scott

We will retrieve this list into a collection of ParentChildDto objects with properties that mirror the database. From this list, we'd like to create a new list of Parent objects each with an appropriate list of AllegedChild objects.

The challenge, given a List<ParentChildDto>, what is a clean and efficient way of creating a List<Parent>? The current implementation looks something like this:

private List<Parent> GitEm( IEnumerable<ParentChildDto> flatList )
{
    rFrom = from p in flatList
            orderby p.ParentId
            select p;

    ParentChildDto prev = null;
    var to = new List<Parent>( );
    foreach( var dto in rFrom )
    {
        if(prev == null || prev.ParentId != dto.ParentId)
        {
            prev = new Parent
                       {
                           Id = dto.ParentId,
                           FullName = dto.Name
                       };
            to.Add(prev);
            prev.AllegedChildren = new List<AllegedChild>();
        }
        if(dto.ChildId != null)
            prev.AllegedChildren.Add(new AllegedChild
                                  {
                                      Id = dto.ChildId,
                                      FullName = dto.Name
                                  });
    }
    return to;
}

While this works, it looks a little too old-school what with the funky indentation and the use of a "previous" placeholder. But try as I might, I can't think of another solution that would be cleaner or more maintainable. There's a chance I may be too "close" to the problem though.

Honorary Hillbilly Status awaits the person who provides an answer deemed worthy of the domain upon which this example is based. Answers must assume a cordial tone and work within the context outlined. Ones that do not (hint: These will be the answers that take the form "Why are you doing XXXX in the first place?" or one of its variations) will be summarily dismissed.

Kyle the Advancefully Grateful

What a difference a day makes. Spent the better part of yesterday trying to figure out the Linq to NHibernate so that I could use Contains on something other than a string, like so:

from job in session.Linq( )
where officeIds.Contains( job.Office.Id )
select job;

The book Linq in Action, as well as Matt Warren's series on query providers, both provided a lot of help in understanding the code. As I started sifting through it and looking at failing tests, I started making some fairly sweeping changes to the code. Not necessarily to check it in, mind you, more for my own eddy-ficashun. I was in spike mode, not "someone else may actually look at this" mode.

In the end, it wasn't quite as hard as I first thought. 'Course, I have a built-in advantage since parsing expression trees is much like parsing the family tree, what with all the AndAlso's and OrElse's. ExclusiveOr was a new concept though.

By the time I was done, I had an ExpressionVisitor very similar to the one Matt Warren specified and I had implemented it in a couple of the visitors in the codebase. It still didn't do what I wanted but I was a lot clearer on how things flowed. It fixed a couple of bugs while introducing others and while I wasn't any further ahead in my specific task, it was a nice break to do something I haven't in a long time.

Then I updated to the latest and lo! Chadly (who I'd link to if I had a blog for him) has implemented what appears to be the same thing, albeit in a much cleaner way. And even better! Doug Mayer (also conspicuously unlinked) has implemented the exact Contains support that I needed! Well, almost. Contains works on arrays now but not on Lists (or anything deriving from ICollection methinks). But ToArray helps out there.

Anyway, here I am a day later having learned about query providers and my work still got done for me in my absence. All hail OSS!

Kyle the NHiberLinq'd