LINQ to NHibernate WHERE EXISTS IN

I've been trying out NHibernate 3 and LINQ to NHibernate. I can't get it to spit out the correct T-SQL query.

Here's my 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 like to select all AttendanceRegistrations between '2010-10-1' and '2010-11-1' that were connected to a certain department at that time.

DateTime start = new DateTime(2010,10,1);
DateTi开发者_Python百科me end = new DateTime(2010,11,1);
var list = 
    from ar in session.Query<AttendanceRegistration>()
    where 
        start <= ar.datetime && ar.datetime > end && (
            from ed in session.Query<EmployeeDepartment>()
            where
                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 will look like this:

select ar.id, ar.datetime, ar.employee_id
from AttendanceRegistration ar
where 
    '2010-10-1 00:00:00' <= ar.datetime and '2010-11-1' > ar.datetime and exists (
    select ed.employee_id
    from EmployeeDepartment ed
    where
        ed.department_id=1 and
        ed.startdate <= ar.datetime and
        ed.enddate > ar.datetime and
        ed.id=ar.employee_id
)

This is ALMOST good :-) The only mistake is

ed.id=ar.employee_id

ed.employee_id=ar.employee_id

Does anybody have ideas how to get LINQ to NHibernate spit out the correct T-SQL query?

I ran into the same problem. I found a way to get around this. Your query can be rewritten as follows. Basically instead of using Contains() operator, add your predicate explicitly in the where clause and use Any() operator.

DateTime start = new DateTime(2010,10,1);
DateTime end = new DateTime(2010,11,1);
var list = 
from ar in session.Query<AttendanceRegistration>()
where 
    start <= ar.datetime && ar.datetime > end && (
        from ed in session.Query<EmployeeDepartment>()
        where
            ed.startdate <= ar.datetime && ed.enddate > ar.datetime &&
            ed.department_id == 1
            && ed.employee_id == ar.employee_id
        select ed
    ).Any()
select ar;