NHibernate OrderBy Projection on Child Objects

Question

I have 2 entities OrganisationMember and User

An OrganisationMember has a object Property called User of type User.

I'm trying to write an NHibernate Query which will do a case insensitive sory by the User.Email property... (users typing emails into the system in various cases).

I've seen that you can use a SQLFunction Projection to basically upper case everything at the DB and order by that. but I'm having trouble applying this to the child object. Here's what I have so far.

var query = Session.QueryOver<OrganisationMembership>();

query.RootCriteria.SetFetchMode("User", FetchMode.Eager);

var projection = Projections.SqlFunction("UPPER", NHibernateUtil.String, Projections.Property("Email"));

query.OrderBy(projection).Asc();

It keeps telling me that it doesn't know what "Email" is in the projection. I've also tried changing the projection to

var projection = Projections.SqlFunction("UPPER", NHibernateUtil.String,  Projections.Property("User.Email"));

Any ideas ?

Answer 1

You have to:

User user = null;

query.JoinAlias(p => p.User, () => user);

equivalent to

query.JoinAlias(p => p.User, () => user, JoinType.InnerJoin);

and then

projection = Projections.SqlFunction("UPPER", 
                                     NHibernateUtil.String,
                                     Projections.Property(() => user.Email));

In NHibernate you always have to explicitly make the Joins. There are no implicit joins.