LINQ to NHibernate在哪里存在 [英] LINQ to NHibernate WHERE EXISTS IN

问题描述

我一直在尝试NHibernate 3和LINQ到NHibernate.我无法吐出正确的T-SQL查询.

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

这是我的域模型:

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"之间的所有AttendanceRegistration.

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);
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代码将如下所示:

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

应该是:

This should have been:

ed.employee_id=ar.employee_id

有人知道如何使LINQ转换为NHibernate吐出正确的T-SQL查询吗?

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

推荐答案

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

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;

这篇关于LINQ to NHibernate在哪里存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！