NHibernate compare concatenated properties
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开发者_Go百科.FirstName + ' ' + x.MiddleInitial)
.IsLike(employeeName, MatchMode.Start)
to no avail.
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
.
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>();
精彩评论