How do I do a String.IsNullOrEmpty() test in an NHibernate Queryover where clause?

Question

I hit a situation today where a field in our legacy db that should never be empty... was empty.

I am using NHibernate 3.2 against this database and the queries that are affected are written in QueryOver.

My current query is this

    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 it to be this:

        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 this I get an exception "Unrecognised method call: System.String:Boolean IsNullOrEmpty(System.String)"

So NHibernate can't translate string.IsNullOrEmpty. Fair enough. However when I try 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' referenced from scope '', but it is not defined"

Any thoughts?

Mcbryde

What happens if you remove the trim()? – Naker

Answer 1

Ok... I guess I asked this question too soon. I figured out a way around this.

What I was able to do was invoke the 'trim' function from hql via a SQL Function Projection. I ended up writing it as IQueryOver Extention method to keep it flexible. I will post it here in case anyone 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 in use

    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);

Mcbryde