Cannot resolve a composite property using QueryOver

Question

In a project I am working on, I'm adopting the newer QueryOver syntax in NHibernate. However, I'm having issues implementing a sort, on a composite property.

The model I'm querying on looks like this:

public class Person
{
    public virtual int Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }

    // Not really relevant; it has an ID, but that's all we care about
    // for this question.
    public virtual Group Group { get; set; }

    // This is the culprit of my troubles.
    public virtual string DisplayName 
    {
        get { return LastName + ", " + FirstName; }
    }
}

...My mapping looks like this:

public class PersonMap : ClassMap<Person>
{
    Table("Persons");

    Id(x => x.Id);
    Map(x => x.FirstName);
    Map(x => x.LastName);

    References(x => x.Group)
        .Not.Nullable()
        .Column("GroupId")
        .Fetch.Join();
}

Note: DisplayName only exists in the server/client stack! Not on the database side.

However, here's where the problem happens: my repository code.

public class PersonRepository
{
    // ...Other methods...

    public static IEnumerable<Person> GetPeopleByGroup(int groupId)
    {
        // This just gets a cached NHibernate session
        using(var session = DataContext.GetSession())
        {
            var results = session
                .QueryOver<Person>()
                .Where(p => p.Group.GroupId == groupId)
                // Exception thrown here!
                .OrderBy(p => p.DisplayName)
                .List().ToList();

            return results;
        }
    }
}

As far as I can tell, this should be working. Question: why can't NHibernate resolve my composite property, despite the fact that both properties that are sourcing the result of that property exist?

Answer 1

Like @Radim Köhler pointed out, the golden QueryOver rule is pretty much "If it's not mapped, you can't query on it".

Even though your property's definition is quite simple, NHibernate's not going to dive into that property and try to understand the implementation and then translate that implementation into SQL.

There are, however, a few workarounds that might apply depending on your situation.

If your solution is working for you, then that's probably what you should go with, since it's so simple. However, there are some other things you could do:

1. Use a computed column and map it to DisplayName.

   I'm not sure what database engine you're using, but if it supports computed columns, then you could actually create a computed column in the database representing DisplayName.

   In SQL server for example:

   alter table [Person] add [DisplayName] as [LastName] + N', ' + [FirstName]
   

   This is straightforward, but it could be incorrect from a separation of concerns perspective to have your database engine care about how a particular row's columns are displayed.

2. Use a Projection:

   Unfortunately, Projections.Concat doesn't take arbitrary Projections, so you'll have to use Projections.SqlFunction (which Projections.Concat uses anyway). You'd end up with something like this:

   var orderByProjection = 
       Projections.SqlFunction(
           "concat",
           NHibernateUtil.String,
           Projections.Property<Person>(p => p.LastName),
           Projections.Constant(", "),
           Projections.Property<Person>(p => p.FirstName));
   
   var people = session.QueryOver<Person>()
       .OrderBy(orderByProjection).Asc
       .List<Person>();
   

3. Tell QueryOver what accessing the DisplayName property means in SQL

   This is pretty involved, but if you want to use DisplayName inside of your QueryOver queries, you can actually tell QueryOver what accessing that property should translate into.

   I actually wouldn't recommend this since it's pretty complicated and it duplicates logic (there would now be two places where DisplayName is defined). That said, it might be useful for others in a similar situation.

   Anyway, if you are curious (or more likely a glutton for QueryOver punishment) here's what that would look like:

   public static class PersonExtensions
   {
       /// <summary>Builds correct property access for use inside of 
       /// a projection.
       /// </summary>
       private static string BuildPropertyName(string alias, string property)
       {
           if (!string.IsNullOrEmpty(alias))
           {
               return string.Format("{0}.{1}", alias, property);
           }
   
           return property;
       }
   
       /// <summary>
       /// Instructs QueryOver how to process the `DisplayName` property access
       /// into valid SQL.
       /// </summary>
       public static IProjection ProcessDisplayName(
           System.Linq.Expressions.Expression expression)
       {
           Expression<Func<Person, string>> firstName = p => p.FirstName;
           Expression<Func<Person, string>> lastName = p => p.LastName;
   
           string aliasName = ExpressionProcessor.FindMemberExpression(expression);
   
           string firstNameName = 
               ExpressionProcessor.FindMemberExpression(firstName.Body);
           string lastNameName = 
               ExpressionProcessor.FindMemberExpression(lastName.Body);
   
           PropertyProjection firstNameProjection = 
               Projections.Property(BuildPropertyName(aliasName, firstNameName));
           PropertyProjection lastNameProjection = 
               Projections.Property(BuildPropertyName(aliasName, lastNameName));
   
           return Projections.SqlFunction(
               "concat",
               NHibernateUtil.String,
               lastNameProjection,
               Projections.Constant(", "),
               firstNameProjection);
       }
   }
   

   Then, you'd need to register the processing logic with NHibernate, probably right after your other configuration code:

   ExpressionProcessor.RegisterCustomProjection(
       () => default(Person).DisplayName,
       expr => PersonExtensions.ProcessDisplayName(expr.Expression));
   

   Finally, you'd be able to use your (unmapped) property inside of a QueryOver query:

   var people = session.QueryOver<Person>()
       .OrderBy(p => p.DisplayName).Asc            
       .List<Person>();
   

   Which generates the following SQL:

   SELECT 
       this_.Id as Id0_0_,
       this_.FirstName as FirstName0_0_,
       this_.LastName as LastName0_0_
   FROM 
       Person this_ 
   ORDER BY 
       (this_.LastName + ', ' + this_.FirstName) asc
   

   You can find more about this technique here. Disclaimer: This is a link to my personal blog.

This is probably way too much information, and personally I'd go for #1 and then #2 if you're not happy with your solution for some reason.

Answer 2

The 'quick-and-dirty' solution to this problem was to OrderBy Last Name, then First Name.

var results = session
    .QueryOver<Person>()
    .Where(p => p.Group.GroupId == groupId)
    .OrderBy(p => p.LastName).Asc()
    .OrderBy(p => p.FirstName).Asc()
    .List().ToList();

I could have also done a Projection, but I felt it less readable. In any event, given a list of sample people...

John Smith
Aberforth Scrooge
Tim Dumbledore
Giselle Potter
John Bane
Kit-Kat Chunky

...The 'correct' order based on my app's rules, and the list generated by this code, is

John Bane
Kit-Kat Chunky
Tim Dumbledore
Giselle Potter
Aberforth Scrooge
John Smith

Case closed...for now. I don't doubt there's incrementally better ways to do this; I am new to QueryOver syntax, after all.