我一直在为即将到来的项目评估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)更多推荐
使用ICriteria的NHibernate相关子查询
发布评论