NHibernate - Implement "NOT IN" query using ICriteria

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

Dozer

Answer 1

Try

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

Easternmost

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. – Dozer

Yup, you can do the same thing with the OR and AND commands as well. – Easternmost

A side note, not sure how other SQL dialects /engines work together, but SQL Server has a query parameter limit of 2100 items, NHibernate populates its IN statements with parameters and will fail if your excludedProductTypeIds exceeds 2100. – Norsworthy

Yeah if you've got that many items it would probably make more sense to use a temp table or some other approach. – Easternmost