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?

Could you explain what you want a bit more clearly? Do you want `StatusFile = 10 OR StatusFile IS NULL`? — Tim Rogers, Apr 17 '13 at 08:06
Hi. I need to retrieve both, StatusFile = 10 and StatusFile is null. — hikizume, Apr 17 '13 at 08:08

Stefan Steinegger · Accepted Answer · 2013-04-17T10:07:00.773

4

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.

edited Apr 17 '13 at 10:07

answered Apr 17 '13 at 08:09

Stefan Steinegger

63,782
15
129
193

This way does not throws an exception the way the one I had does, but nhibertate interprets it "StatusFile in (10) and StatusFile is null", and it does not retrieve any values. – hikizume Apr 17 '13 at 08:25
here is an explenation for an or: http://stackoverflow.com/questions/3299022/nhibernate-or-criteria-query – Verena Haunschmid Apr 17 '13 at 11:16

score 2 · Answer 2 · answered Apr 17 '13 at 08:48

2

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

answered Apr 17 '13 at 08:48

Tim Rogers

21,297
6
52
68

mridula · Answer 3 · 2013-04-17T09:44:12.677

.List() Since Stefan Steinegger's answer is 'and'ing your critieria, why don't you try disjunction ('or'ing)? Something like this, -

var query = Session.QueryOver<PersistentType>();
var disjunction = new Disjunction();   
disjunction.Add(Restrictions.On<PersistentType>(obj => obj.statusFile == 10));
disjunction.Add(Restrictions.On<PersistentType>(obj => obj.statusFile == null));
var queryResult = query.Where(disjunction).List<PersistentType>();

Or simply,

var queryResult = Session.QueryOver<PersistentType>()
    .Where(obj => obj.statusFile == 10 || obj.statusFile == null).List<PersistentType>();

nhibernate c# criteria to retrieve null values

3 Answers3