由于 几乎 总是，NHibernate的确实有这个答案。我们在这里想要实现的是这样的SQL语句查找：
// final Request selectionSELECT request.[RequestId] FROM [Request] request // only requests, which are successful, and have Max(date) WHERe request.[RequestId] IN ( SELECt successResponse.RequestId as y0_ FROM [Response] successResponse // response which max date is equal to the upper response // and which RequestId corresponds with supper upper Request WHERe EXISTS ( SELECt maxResponse.RequestId as y0_, max(maxResponse.[DateTime]) as y1_FROM [Response] maxResponse// do the MAX only for current RequestWHERe maxResponse.RequestId = successResponse.RequestId GROUP BY maxResponse.RequestId// assure that the Response match is on the max DateTimeHAVINg max(maxResponse.[DateTime]) = successResponse.[DateTime] ) AND successResponse.[Success] = 1 )
笔记：
- 期望 响应 确实有
RequestId
- 上面是用C＃
//
注释代替SQL--
现在，NHibernate和QueryOver的神奇之处在于：
// This declaration will allow us, to use a reference from middle SELECT// in the most deeper SELECTResponse response = null;// the most INNER SELECTvar maxSubquery = QueryOver.Of<Response>() .SelectList(l => l .SelectGroup(item => item.RequestId) .SelectMax(item => item.DateTime) ) // WHERe Clause .Where(item => item.RequestId == response.RequestId) // HAVINg Clause .Where(Restrictions.EqProperty( Projections.Max<Response>(item => item.DateTime), Projections.Property(() => response.DateTime) ));// the middle SELECTvar successSubquery = QueryOver.Of<Response>(() => response) // to filter the Request .Select(res => res.RequestId) .WithSubquery .WhereExists(maxSubquery) // now only these wich are successful .Where(success => success.Success == true) ;
此时，我们必须嵌套内部SUB SELECT。让我们使用它们：
// the most outer SELECTvar query = session.QueryOver<Request>();query.WithSubquery // our Request ID is IN(... .WhereProperty(r => r.ID) .In(successSubquery);var list = query .List<Request>();
最后说明，我没有在讨论这个概念。没有表现。 我宁愿在响应“ IsActive”上使用一个设置，并使其更容易 …这仅仅是如何做到这一点的答案…
