NHibernate - Implement “NOT IN” query using ICrite

Question

I've started getting to grips with NHibernate. I'm trying to perform a query that selects all records from a table but with an exclusion filter list of IDs, eg. get me all Products except these ones with these ID values.

Normally in direct T-SQL I'd pass in the IDs to be excluded into a NOT IN clause like so.

SELECT *
FROM Products
WHERE ProductId NOT IN (1,5,9,23,45)

How do I do this in NHibernate using either ICriteria or HQL (but preferably ICriteria)?

Answer 1

Try

.Add(Expression.Not(Expression.In("ProductID", new int[] { 1, 5, 9, 23, 45 })))