1

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.

Jonn
  • 4,599
  • 9
  • 48
  • 68

2 Answers2

2

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.

rebelliard
  • 9,592
  • 6
  • 47
  • 80
  • @Jonn - Look at http://codebetter.com/kylebaley/2010/07/30/using-an-nhibernate-formula-to-aid-searching/ – rebelliard May 26 '11 at 17:58
  • Thanks. Works like a charm. I've got one consideration though. Won't this affect performance? This means an additional computed field every time I retrieve an employee. – Jonn May 27 '11 at 00:52
  • I haven't felt it affect performance, and considering how most of the time you would end up doing this anyways (at least I know I do) and how it helps me with searching, I really don't care for microseconds in performance loss. Do mark as answered. :D – rebelliard May 27 '11 at 02:33
  • It works. Why not? XD I'll just trouble myself later, if ever I do get performance issues. – Jonn May 27 '11 at 07:19
0
ICriteria criteria = session.CreateCriteria(typeof(IPersonnel));
        criteria.CreateCriteria("Personnel", "p");
        criteria.Add(Restrictions.Like("p.LastName + p.FirstName + p.MiddleInitial", employeeName)); 
        criteria.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
        return criteria.List<IPersonnel>();
Oleg
  • 798
  • 3
  • 7
  • Doesn't work. That's the same as my previous attempt, only in classic ICriteria. – Jonn May 26 '11 at 09:45
  • @Jonn, then it's probably a problem with your mappings. Maybe you can provide more input? Your initial question does not even state that you have any problem :) – Oleg May 26 '11 at 10:10
  • From what I understand, your code is the same as the attempt I made (posted above), only using a different syntax. It returns a `QueryException "Could not resolve property: p.Lastname + p".` I think NHibernate only accepts properties mapped to the database for criteria and not formulas. Would be glad if you'd prove me wrong though. – Jonn May 27 '11 at 00:47