nhibernate c# criteria to retrieve null values

Question

I need to retrieve files with a status file = 10 and null values form a nullable VARCHAR2 column from an oracle db.

After some searching I found the following:

ICriteria criteria = NHibernateSession.CreateCriteria(persitentType);  
criteria.Add(Expression.In("StatusFile", 10));
criteria.Add(Restrictions.IsEmpty("StatusFile"));

In sql would be something like:

select attstatus from table where file_tmode = 'P'and  (status is null or status = 10);

If I remove the last line, it works, but I have not been able to find a way to add the criteria for the null values.

How could I do this?

Answer 1

Did you try IsNull?

NHibernateSession.CreateCriteria(persitentType)
  .Add(Expression.In("StatusFile", 10))
  .Add(Expression.IsNull("StatusFile"));

using or

NHibernateSession.CreateCriteria(persitentType)
  .Add(Expression.In("StatusFile", 10)
    || Expression.IsNull("StatusFile"));

Note that nulls are not indexed in oracle (at least a few years ago when I used it) and it may be very slow to find null values in a large table.

Answer 2

ICriteria criteria = NHibernateSession.CreateCriteria(persitentType);  
criteria.Add(Restrictions.Or (
    Restrictions.Eq ("StatusFile", 10), 
    Restrictions.IsNull ("StatusFile)
));