NHibernate compare concatenated properties

Question

How would you do this

Select *
from Personnel p
where p.LastName + ', ' + p.FirstName + ' ' + p.MiddleInitial LIKE @Employee + '%'

using NHibernate (3.0)? So far, I've tried

personnel.QueryOver<Personnel>()
    .WhereRestrictionOn( x => x.LastName + ', ' + x.FirstName + ' ' + x.MiddleInitial)
    .IsLike(employeeName, MatchMode.Start)

to no avail.

Answer 1

If you mapped those three columns as a single property using Formula, it will work:

public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        Table("Employees");

        Id(x => x.Id);
        Map(x => x.Name)
          .Formula("UPPER(LTRIM(RTRIM(FirstName + ' ' + MiddleName + ' ' + LastName)))");

        // etc.
    }
}

That's an example using SQL Server, in Oracle, you would switch the ' for | and ditch LTRIM and RTRIM.