How to run String.IsNullOrEmpty () test in NHibernate Queryover, where is the condition?

Question

Today I got into a situation where the field in our previous db, which should never be empty ... was empty.

I am using NHibernate 3.2 for this database, and queries that are affected are recorded in QueryOver.

My current request is

return Session .QueryOver<FacilityGroup>() .Where(fg => fg.Owner.Id == Token.OwnerId && fg.UserName == Token.UserName) .OrderBy(fg => fg.Code).Asc .TransformUsing(Transformers.DistinctRootEntity); 

I want this to be:

  return Session .QueryOver<FacilityGroup>() .Where(fg => fg.Owner.Id == Token.OwnerId && fg.UserName == Token.UserName && !string.IsNullOrEmpty(fg.Code)) .OrderBy(fg => fg.Code).Asc .TransformUsing(Transformers.DistinctRootEntity); 

When I try to do this, I get the exception "Unrecognized method call: System.String: Boolean IsNullOrEmpty (System.String)"

So NHibernate cannot translate string.IsNullOrEmpty. Fair. However, when I try to do this

  return Session .QueryOver<FacilityGroup>() .Where(fg => fg.Owner.Id == Token.OwnerId && fg.UserName == Token.UserName && !(fg.Code == null || fg.Code.Trim() == "" )) .OrderBy(fg => fg.Code).Asc .TransformUsing(Transformers.DistinctRootEntity); 

I get an InvalidOperationException variable "fg" of type "Domain.Entities.FacilityGroup", which refers to the scope "", but it is not defined "

Any thoughts?

Answer 1

Okay ... I think I asked this question too soon. I thought about that.

What I was able to do was call the "trim" function from hql via the SQL Function Projection. In the end, I wrote it as an IQueryOver Extention method so that it is flexible. I will post it here if someone needs it.

 public static class QueriesExtentions { public static IQueryOver<E, F> WhereStringIsNotNullOrEmpty<E, F>(this IQueryOver<E, F> query, Expression<Func<E, object>> propExpression) { var prop = Projections.Property(propExpression); var criteria = Restrictions.Or(Restrictions.IsNull(prop), Restrictions.Eq(Projections.SqlFunction("trim", NHibernateUtil.String, prop), "")); return query.Where(Restrictions.Not(criteria)); } } 

and here it is used

  return Session .QueryOver<FacilityGroup>() .Where(fg => fg.Owner.Id == Token.OwnerId && fg.UserName == Token.UserName ) .WhereStringIsNotNullOrEmpty(fg => fg.Code) .OrderBy(fg => fg.Code).Asc .TransformUsing(Transformers.DistinctRootEntity);