views:

702

answers:

1

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)?

+5  A: 

Try

.Add(Expression.Not(Expression.In("ProductID", new int[] { 1, 5, 9, 23, 45 })))
Spencer Ruport
Awesome, that worked perfectly, thanks. Here is the full code I ended up implementing...query.Add(Expression.Not(Expression.In("ProductType.Id", excludedProductTypeIds.ToArray())));I saw the 'Not' and the 'In' methods, but didn't think to chain them like that.
Sunday Ironfoot
Yup, you can do the same thing with the OR and AND commands as well.
Spencer Ruport