How to Retrieve Requests with Only the Latest Successful Response Using NHibernate?

Query a HasMany reference using NHibernate: Retrieve the request with the latest successful response

This article will guide you in building a query that retrieves all requests whose latest response (based on the timestamp attribute) was successful.

Consider the following solid model:

<code class="language-c#">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; }
}</code>

Query building:

To obtain the desired results, we can create an NHibernate QueryOver query that utilizes a subquery:

<code class="language-c#">// 响应子查询，查找最大日期响应
var maxSubquery = QueryOver.Of<Response>()
    .SelectList(l => l
        .SelectGroup(item => item.RequestId)
        .SelectMax(item => item.Timestamp))
    .Where(item => item.RequestId == response.RequestId)
    .Where(Restrictions.EqProperty(
        Projections.Max<Response>(item => item.Timestamp),
        Projections.Property(() => response.Timestamp)));

// 响应子查询，查找具有最大日期的成功响应
var successSubquery = QueryOver.Of<Response>()(() => response)
    .Select(res => res.RequestId)
    .WithSubquery
    .WhereExists(maxSubquery)
    .Where(success => success.Success == true);

// 基于成功响应过滤的请求查询
var query = session.QueryOver<Request>();
query.WithSubquery
    .WhereProperty(r => r.ID)
    .In(successSubquery);

var list = query
    .List<Request>();</code>

Query instructions:

• maxSubquery Find the maximum date response for each request.
• successSubquery Filters responses to only include those that are successful and have a maximum date for their respective requests.
• The main query then filters requests based on the availability of successful responses identified in successSubquery.

Alternatively, consider setting an "IsActive" property on the response to simplify querying.

The above is the detailed content of How to Retrieve Requests with Only the Latest Successful Response Using NHibernate?. For more information, please follow other related articles on the PHP Chinese website!