Create select sub-query on NHibernate

On this sample, I will show you how to create select sub-query on NHibernate by using DetachedCriteria and Projections.

For example, I have a model with following structure:

public partial class Auction
{
    public virtual long AuctionID { get; set; }
    public virtual IList<MaxBid> MaxBids { get; set; }

    public virtual DateTimeOffset EndDate { get; set; }
}

public partial class Account {
  public virtual long AccountID { get; set; }
}

    
public partial class MaxBid {
        public virtual long MaxBidID { get; set; }
        public virtual Auction Auction { get; set; }
        public virtual Account BuyerAccount { get; set; }
    }

public partial class Bid {
	public virtual long BidID { get; set; }
	public virtual MaxBid MaxBid { get; set; }
    public virtual bool IsRetracted { get; set; }
}

We have to create a query to get all auction, on each auction, we have to determine the current bid price (the latest bid record) and the current buyer’s max bid price (the latest max bid record by buyer) without retraction status. We can easily create a SQL Transact query as following (on this example, buyer account id = 1):

SELECT Auction.AuctionID
	, Auction.AssetClassID
	, Auction.EndDate
	, (SELECT MAX(Bid.BidPrice) FROM MaxBid LEFT JOIN Bid ON MaxBid.MaxBidID = Bid.MaxBidID 
		WHERE MaxBid.AuctionID = Auction.AuctionID AND MaxBid.IsRetracted = 0
		) AS CurrentBid
	, (SELECT MAX(MaxBid.MaxBidPrice) FROM MaxBid 
		WHERE MaxBid.AuctionID = Auction.AuctionID 
		AND MaxBid.IsRetracted = 0 AND MaxBid.BuyerAccountID = 1
		) AS MyMaxBid  
FROM Auction

This query select all auction, each auction, we have to get maximum bib price without retraction and buyer’s maximum bid price.

We can create NHibernate query to do this as following:

var myMaxBidQuery =
    DetachedCriteria.For<MaxBid>("mb")
    .Add(Restrictions.Eq("mb.IsRetracted", false))
    .Add(Restrictions.Eq("mb.BuyerAccount.AccountID", 1L))
    .Add(Property.ForName("mb.Auction.AuctionID").EqProperty("a.AuctionID"))
    .SetProjection(Projections.Max("mb.MaxBidPrice"));

var currentBidQuery =
    DetachedCriteria.For<MaxBid>("mb")
    .CreateAlias("mb.Bids", "b", JoinType.LeftOuterJoin)
    .Add(Restrictions.Eq("mb.IsRetracted", false))
    .Add(Property.ForName("mb.Auction.AuctionID").EqProperty("a.AuctionID"))
    .SetProjection(Projections.Max("b.BidPrice"));

var query =
    DetachedCriteria.For<Auction>("a")
        .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("a.AuctionID"))
        .Add(Projections.Property("a.AssetClass.AssetClassID"))
        .Add(Projections.Property("EndDate"))
        .Add(Projections.SubQuery(currentBidQuery))
        .Add(Projections.SubQuery(myMaxBidQuery)))
        .SetResultTransformer(Transformers.AliasToBean<DataResult>());

var results = query.GetExecutableCriteria(session).List<DataResult>();

In this case, I created two DetachedCriteria objects to perform sub-queries, each sub-query has a Property.ForName to put the relation to main query by equal property (EqProperty). And on the main query, they will be add into main query by using Projections.SubQuery. And the result, I used Transformers to transform the result to customized bean based on the structure of query, we can create transformer result class as following code snippet:

public class DataResult
{
    public long AuctionID { get; set; }
    public int AssetClassID { get; set; }
    public DateTimeOffset EndDate { get; set; }
    public decimal MaxBidPrice { get; set; }
    public decimal BidPrice { get; set; }
}

Hope that by this sample, I can help you to create more complex queries using DetachedCriteria, Projections and Transformer on NHibernate and of course, we can translate it to Hibernate on Java.