nhibernate - LINQ to NHibernate WHERE EXISTS IN

Question

我一直在尝试 NHibernate 3 和 LINQ to NHibernate。我无法让它吐出正确的 T-SQL 查询。

这是我的域模型:

Employee { id, name }
Department { id, name }
EmployeeDepartment { id, employee_id, department_id, startdate, enddate }
AttendanceRegistration { id, datetime, employee_id }

现在假设我想选择当时连接到某个部门的“2010-10-1”和“2010-11-1”之间的所有出席登记。

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
            select ed.employee_id
    ).Contains(ar.employee_id)
    select ar;

生成的 SQL 代码如下所示:

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
)

这几乎很好:-) 唯一的错误是

ed.id=ar.employee_id

这应该是:

ed.employee_id=ar.employee_id

有没有人知道如何让 LINQ to NHibernate 吐出正确的 T-SQL 查询？

Answer 1

我遇到了同样的问题。我找到了解决这个问题的方法。您的查询可以改写如下。基本上不是使用 Contains() 运算符，而是在 where 子句中显式添加谓词并使用 Any() 运算符。

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;