LINQ to NHibernate WHERE EXISTS IN -

i've been trying out nhibernate 3 , linq nhibernate. can't spit out correct t-sql query.

here's domain model:

employee { id, name } department { id, name } employeedepartment { id, employee_id, department_id, startdate, enddate } attendanceregistration { id, datetime, employee_id } 

now suppose i'd select attendanceregistrations between '2010-10-1' , '2010-11-1' connected department @ time.

datetime start = new datetime(2010,10,1); datetime end = new datetime(2010,11,1); var list =      ar in session.query<attendanceregistration>()              start <= ar.datetime && ar.datetime > end && (             ed in session.query<employeedepartment>()                             ed.startdate <= ar.datetime && ed.enddate > ar.datetime &&                 ed.department_id = 1             select ed.employee_id     ).contains(ar.employee_id)     select ar; 

the resulting sql code this:

 select ar.id, ar.datetime, ar.employee_id attendanceregistration ar      '2010-10-1 00:00:00' <= ar.datetime , '2010-11-1' > ar.datetime , exists (     select ed.employee_id     employeedepartment ed             ed.department_id=1 ,         ed.startdate <= ar.datetime ,         ed.enddate > ar.datetime ,         ed.id=ar.employee_id ) 

this :-) mistake

 ed.id=ar.employee_id 

should have been:

 ed.employee_id=ar.employee_id 

does have ideas how linq nhibernate spit out correct t-sql query?

i ran same problem. found way around this. query can rewritten follows. instead of using contains() operator, add predicate explicitly in clause , use any() operator.

datetime start = new datetime(2010,10,1); datetime end = new datetime(2010,11,1); var list =  ar in session.query<attendanceregistration>()      start <= ar.datetime && ar.datetime > end && (         ed in session.query<employeedepartment>()                     ed.startdate <= ar.datetime && ed.enddate > ar.datetime &&             ed.department_id == 1             && ed.employee_id == ar.employee_id         select ed     ).any() select ar;