我一直在尝试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在哪里存在
发布评论