查询HasMany参考

  • Z时代
  • 2024-01-10
  • 分类：问答

我有一个这样的实体模型：

public class Request
{
    public virtual IList<Response> Responses { get; set; }
}
public class Response
{
    public virtual DateTime Timestamp { get; set; }
    public virtual bool Success { get; set; }
}

我正在尝试创建一个 查询 ，该 查询 将为我提供所有 请求 ，该 请求 的最新 响应 （就其 时间戳而言 ）是 成功的

。如何才能做到这一点？

回答：

由于 几乎 总是，NHibernate的确实有这个答案。我们在这里想要实现的是这样的SQL语句查找：

// final Request selection
SELECT 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 Request
           WHERE maxResponse.RequestId = successResponse.RequestId 
           GROUP BY maxResponse.RequestId
           // assure that the Response match is on the max DateTime
           HAVING max(maxResponse.[DateTime]) = successResponse.[DateTime]
        ) 
        AND successResponse.[Success] = 1
   )

笔记：

  1. 期望 响应RequestId

  2. 上面是用C＃//注释代替SQL--

现在，NHibernate和QueryOver的神奇之处在于：

// This declaration will allow us, to use a reference from middle SELECT
// in the most deeper SELECT
Response response = null;
// the most INNER SELECT
var 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 SELECT
var 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 SELECT
var query = session.QueryOver<Request>();
query.WithSubquery
    // our Request ID is IN(...
    .WhereProperty(r => r.ID)
    .In(successSubquery);
var list = query
    .List<Request>();

最后说明，我没有在讨论这个概念。没有表现。 我宁愿在响应“ IsActive”上使用一个设置，并使其更容易 …这仅仅是如何做到这一点的答案…

以上是 查询HasMany参考 的全部内容， 来源链接： utcz.com/qa/397579.html

回到顶部