Using NHibernate query to query NOT IN with JOIN statement

Question

select alphanum from sampler where alphanum not in (select sampler.alphanum from sampler, samplerassignment where sampler.alphanum = samplerassignment.alphanum and isactive = 1);

I have this statement and would like to use NHibernate query to execute it. How shld I write it in NHibernate?

Answer 1

Try:

string hql = @"select s1.alphanum from sampler s1 where 
               s1.alphanum not in 
               (select s2.alphanum from sampler s2, samplerassignment sa where s2.alphanum = sa.alphanum and sa.isactive = 1)";

var result = session.CreateQuery(hql).List();

where session is your NHibernate's ISession.