Using NHibernate to query with NOT IN in the WHERE clause

Question

Take this query as an example:

select * from publisher 
where id not in (
    select publisher_id from record 
    where year = 2008 and month = 4
)

Can anyone help me on how I could build and run this query using NHibernate? Assume that I have 2 classes: Publisher and Record.

Thanks

Answer 1

Try this:

DetachedCriteria c = DetachedCriteria.For<Record>()
    .SetProjection(Projections.Property("Publisher"))
    .Add(Restrictions.Eq("Year", 2008))
    .Add(Restrictions.Eq("Month", 4));
session.CreateCriteria(typeof(Publisher))
    .Add(Subqueries.PropertyNotIn("Id", c))
    .List();