Nhibernate C# - Return only objects where string property is not null or empty.

Question

I'm writing a query using ICriteria that should return only the objects where property "Message" has value (i.e. is not null or empty). Below is the format I'm using.

ICriteria.Add(Restrictions.Not(Restrictions.Eq("Message", " "));
ICriteria.Add(Restrictions.Not(Restrictions.Eq("Message", String.Empty));

Can somebody help me with this? Thank you!

Answer 1

You might want something like:

ICriteria a = session.CreateCriteria(typeof(A));
a.add(Restrictions.Not(Restrictions.Eq("Message", " "));
a.add(Restrictions.Not(Restrictions.Eq("Message", string.Empty));

Although, your first one isn't really checking null, it's checking a single space.

Answer 2

I've not tried this, but it thnk the following should work:

ICriteria crit = session.CreateCriteria(typeof(theType))
                   .Add(Restrictions.IsNotNull("Message"))
                   .Add(Restrictions.IsNotEmpty("Message"));

Answer 3

Finally, I discovered the combination I was looking for!

lvCriteria.Add(Restrictions.Not(Expression.Eq("Msg", string.Empty)));

This combination of Restrictions and Expression works as expected; narrowing out all empty strings. I do not know why I could not achieve these results earlier even with:

lvCriteria.Add(Restrictions.Not(Restrictions.Eq("Msg", string.Empty)));

Thank you to all who tried.

Answer 4

What you really need is:

ICriteria crit = session.CreateCriteria(typeof(theType))
.Add(Restrictions.Not(Restrictions.Eq("Msg", string.Empty)))
.Add(Restrictions.IsNotNull("Msg"))

This is working fine for me and profiler shows correct SQL i.e. something like:

msg<>'' and msg is not null. 

First answer did not work for me as Restrictions.IsNotEmpty/Empty applies only to collections (in NH 2.0.1 at least).