1

Is it possible to make an Nhibernate query generate Sql that has columnName<>'value' rather than not(columnName='value')?

I am told that using not() can have Sql performance issues over <>.

Typically I am writing my queries like this...

criteria.Add(Restrictions.WhereNot<Region>(r => r.Id == region.Id));

which results in

WHERE  not (this_.RegionID = 2048)

UPDATE

This question suggests that there is no longer any performance issues with coding one way or the other

In SQL Server is there any difference between not(columnName='value') and columnName<>'value'?

Community
  • 1
  • 1
Simon Keep
  • 9,886
  • 9
  • 63
  • 78
  • 1
    They should be optimized identically for any reasonable SQL product (certainly for SQL Server), so I'd challenge the assumption of a performance issue in the first place. – Damien_The_Unbeliever Jan 11 '12 at 09:53
  • I think the option not available in criteria api.But you can use criteria.Add(Expression.Sql("columnName <>'value'")); – Anand Jan 11 '12 at 11:00
  • @an2 Yes that's a useful work around thanks. if you add it as an answer I'll accept it assuming that nothing else comes up. – Simon Keep Jan 11 '12 at 12:22

1 Answers1

2

I think that option is not available in criteria api.But you can use Expression.Sql() as follow 

criteria.Add(Expression.Sql("columnName <>'value'"));
Anand
  • 717
  • 6
  • 20