NHibernate Criteria - How to filter on combination of properties
- by DavGarcia
I needed to filter a list of results using the combination of two properties. A plain SQL statement would look like this:
SELECT TOP 10 *
FROM Person
WHERE FirstName + ' ' + LastName LIKE '%' + @Term + '%'
The ICriteria in NHibernate that I ended up using was:
ICriteria criteria = Session.CreateCriteria(typeof(Person));
criteria.Add(Expression.Sql(
"FirstName + ' ' + LastName LIKE ?",
"%" + term + "%",
NHibernateUtil.String));
criteria.SetMaxResults(10);
It works perfectly, but I'm not sure if it is the ideal solution since I'm still learning about NHibernate's Criteria API. What are the recommended alternatives?
Is there something besides Expression.Sql that would perform the same operation? I tried Expression.Like but couldn't figure out how to combine the first and last names.
Should I map a FullName property to the formula "FirstName + ' ' + LastName" in the mapping class?
Should I create a read only FullName property on the domain object then map it to a column?
