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.

LINQ to Entities: PredicateBuilder and Dynamic Linq

When you want to build dynamic filter for a query on LINQ to SQL or LINQ to Entities (Entity Framework, NHibernate, ..), you can use a famous Albahari's PredicateBuilder. But sometime, it may be doesn't work perfect with LINQ to Entities. To solve this problem, Pete Montgomery introduced a universal PredicateBuilder that combined the original version of Albahari's PredicateBuilder and LINQ to Entities: Combining Predicates.

Apache: Deny access from everyone, except your IP address

To block all visitors to access your website except your IP address and customers’. You can add  following codes at the end of httpd.conf file and restart your httpd.

# Deny access from everyone, except those IP listed here:
<Files *>
  Order deny,allow
  Deny from all
  Allow from xxx.xxx.xxx.xxx
  Allow from yyy.yyy.yyy.yyy
</Files>