NHibernate Criteria - How to filter on combination of properties
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 l开发者_高级运维earning about NHibernate's Criteria API. What are the recommended alternatives?
- Is there something besides
Expression.Sql
that would perform the same operation? I triedExpression.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?
You can do one of the following:
- If you always work with the full name, it's probably best to have a single property
- Create a query-only property for that purpose (see http://ayende.com/Blog/archive/2009/06/10/nhibernate-ndash-query-only-properties.aspx)
- Do the query in HQL, which is better suited for free-form queries (it will probably be almost the same as your SQL)
- Use a proper entity-based Criteria:
Session.CreateCriteria<Person>()
.Add(Restrictions.Like(
Projections.SqlFunction("concat",
NHibernateUtil.String,
Projections.Property("FirstName"),
Projections.Constant(" "),
Projections.Property("LastName")),
term,
MatchMode.Anywhere))
On the pure technical side i don't have an answer, but consider this: since you are only have a single input field for the user to enter the term, you don't know if he is going to enter 'foo bar' or 'bar foo'... so i would recommend this:
ICriteria criteria = Session.CreateCriteria(typeof(Person));
criteria.Add(Expression.Like("FirstName",term, MatchMode.Anywhere) || Expression.Like("LastName",term, MatchMode.Anywhere));
criteria.SetMaxResults(10);
精彩评论