使用ICriteria的NHibernate相关子查询

本文介绍了使用ICriteria的NHibernate相关子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我一直在为即将到来的项目评估NHibernate的工作，并正在研究一些用例以查看其性能.我尚未找到使用Criteri API来表达以下查询的方法.

I've been doing some work evaluating NHibernate for an upcoming project and am working through some use cases to see how it performs. I haven't yet been able to find a way to express the following query using the Criteri API.

两个相当基本的表(出于本示例的目的而被删减)

Two fairly basic tables (cut down for the purpose of this example)

CREATE TABLE Person ( PersonNo INT, BirthDate DATETIME ) CREATE TABLE PersonDetails ( PersonNo INT, FirstName VARCHAR(30), Surname VARCHAR(30) )

查询...

SELECT P.PersonNo, P.FirstName, P.Surname FROM Persons P JOIN PersonDetails PD ON PD.PersonNo = P.PersonNo AND EffDate = ( SELECT MAX(EffDate) FROM PersonDetails WHERE PersonNo = PD.PersonNo ) WHERE P.PersonNo = 1

基本上，我只是试图将人员主记录和最新人员修订记录拼合为一个对象.我可以使用HQL轻松地完成此操作，但无法使相关子查询正常工作.

Basically, I am just trying to flatten the person master record and the latest person revision record into one object. I was able to do this easily enough using HQL but cannot get the correlated subquery to work.

这是我的尝试.

var pdSub = DetachedCriteria.For<PersonRevision>("pdSub") .SetProjection( Projections.ProjectionList() .Add(Projections.Max("EffDate").As("MaxEffDate")) .Add(Projections.Property("Person.PersonNo").As("PersonNo")) .Add(Projections.GroupProperty("Person.PersonNo"))) .Add(Expression.EqProperty("pdSub.Person.PersonNo", "p.PersonNo")); var p = session.CreateCriteria<Person>("p") .Add(Restrictions.Eq("p.PersonNo", 1)) .Add(Subqueries.Eq("p.PersonNo", pdSub)) .List();

子查询pdSub已经定义了该关系(通过PersonNo)，但是Subqueries类需要定义另一个关系(例如Eq)?

The subquery pdSub already defines the relationship (by PersonNo) but the Subqueries class requires another relationship to be defined (e.g. Eq)?

任何帮助将不胜感激.

谢谢， 约翰

推荐答案

出于演示的目的，我在两个表中都添加了EffDate.希望能与您的模型匹配，并适当地演示这种连接.

For the purposes of a demo I've added EffDate to both tables. Hopefully that matches your model and demonstrates this kind of join appropriately.

DetachedCriteria subQuery = DetachedCriteria .For<PersonDetails>("pd") .SetProjection(Projections.Max("pd.EffDate")) .Add(Restrictions.EqProperty("pd.PersonId", "p.PersonId")); IList results = Session .CreateCriteria(typeof(Person), "p") .SetProjection(Projections.ProjectionList() .Add(Projections.Property("p.PersonId").As("PersonId")) .Add(Projections.Property("p.EffDate").As("MaxEffDate"))) .Add(Subqueries.PropertyEq("p.EffDate", subQuery)) .List();

SQL NHibernate抛出在服务器上看起来像这样...

The SQL NHibernate is throwing at the server looks like this...

SELECT this_.PersonId as y0_, this_.EffDate as y1_ FROM Person this_ WHERE this_.EffDate = (SELECT max(this_0_.EffDate) as y0_ FROM PersonDetails this_0_ WHERE this_0_.PersonId = this_.PersonId)