Fluent NHibernate Querying: OrderBy column of joined table

Question

I've been struggling quite some time now with trying to query some stuff using NHibernate. I've managed to do some very simple queries, but now I'm trying to build some custom pagination through NHibernate.

What I want to do is twofold. First (1) off, I want to be able to order my resultset based on a column from a joined table. Let's say I have a 'Person' table that has a one-to-one (actually many-to-one) reference with an 'Address' table. I want to query over the 'Person' table (using its fields), but want to sort using fields from the 'Address' table.

How do I do this? I've tried the following two ways

var resultset = GetCurrentSession().QueryOver<Person>()
                   .Where(x => x.Name == "...")
                   .JoinQueryOver<Address>(x => x.Address)
                   .OrderBy(x => x.HouseNumber).Desc
                   .Skip(...)
                   .Take(...)
                   .List();
// ==> could not execute query

Person person = null;
Address address = null;
var resultset = GetCurrentSession().QueryOver<Person>()
                   .JoinAlias(() => person, () => address.Persons)
                   .Where(() => person.Name == "...")
                   .OrderBy(() => address.HouseNumber).Asc
                   .Skip(...)
                   .Take(...)
                   .List();
// ==> could not resolve property: person of: Person

My second (2) question is, I would like to split the ordering up. Based on conditional statements, I want to add a different OrderBy. Can I just do it like so?

var query = GetCurrentSession().QueryOver<Person>()
               .Where(x => x.Name == "...")
               .JoinQueryOver<Address>(x => x.Address);

if(foo)
{
    query = query.OrderBy(() => address.HouseNumber).Asc
}
else if (bar)
{
    query = query.OrderBy(() => address.Street).Desc
}

var resultset = query                    
                   .Skip(...)
                   .Take(...)
                   .List();

Thanks a lot!

Answer 1

All of your examples should work, with the small caveat that you need to assign your aliases properly in order to use them (the message could not resolve property: person of: Person indicates that person was never actually set as the alias). For example:

Person person = null;
Address address = null;

// Assign the person alias using () => person
var resultset = GetCurrentSession().QueryOver<Person>(() => person)
    .JoinAlias(() => person.Address, () => address) // Assign the address alias here
    .Where(() => person.Name == "...")
    .OrderBy(() => address.HouseNumber).Asc
    .Skip(...)
    .Take(...)
    .List();

This should work fine.

As for your second question, what you're trying to achieve is very possible, an in fact a great reason to use QueryOver to begin with. You just need a few tweaks:

var query = GetCurrentSession().QueryOver<Person>()
               .Where(x => x.Name == "...")
               .JoinQueryOver<Address>(x => x.Address, () => address); // assign address alias

if(foo)
{
    query.OrderBy(() => address.HouseNumber).Asc(); // <-- use the *method* .Asc()
}
else if (bar)
{
    query.OrderBy(() => address.Street).Desc(); // <-- use the *method* .Desc()
}

Note that since .Asc() and .Desc() actually modify the query, you don't need to reassign the query. Be sure to use the methods .Asc() and .Desc() so that you don't get a build error.