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
– Tim Rogers, Commented Apr 17, 2013 at 8:06
Hi. I need to retrieve both, StatusFile = 10 and StatusFile is null. — hikizume
– hikizume, Commented Apr 17, 2013 at 8:08

Stefan Steinegger · Accepted Answer · 2013-04-17 10:07:00Z

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, 2013 at 10:07

answered Apr 17, 2013 at 8:09

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hikizume Over a year ago

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.

Verena Praher Over a year ago

here is an explenation for an or: stackoverflow.com/questions/3299022/…

Tim Rogers · Accepted Answer · 2013-04-17 08:48:08Z

2

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

answered Apr 17, 2013 at 8:48

Tim Rogers

21.8k8 gold badges55 silver badges69 bronze badges

Comments

mridula · Accepted Answer · 2013-04-17 09:44:12Z

0

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

edited Apr 17, 2013 at 9:44

answered Apr 17, 2013 at 9:23

mridula

3,3113 gold badges34 silver badges58 bronze badges

Collectives™ on Stack Overflow

nhibernate c# criteria to retrieve null values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related