CODEWITHSUNDEEP
c#nhibernate
rguerreiro
rguerreiro

Reputation: 2683

Using NHibernate to query with NOT IN in the WHERE clause

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

Upvotes: 6

Views: 4861

Answers (1)

Stuart Childs
Stuart Childs

Reputation: 3305

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();

Upvotes: 7

Related Questions