How to query a subproperty with NHibernate’s criteria api and the entity to load only the subproperties matching a predicate condition

Question

Assuming the following:

public class Order
{
   public virtual int OrderId {get;set}
   public virtual ISet<Product> Products {get;set}
}

public class Product
{
   public virtual int ProductId {get;set}
   public virtual string ProductName {get;set}
}

How would you query using the criteria api so that only an order with a specific orderid is returned and its Product collection should also be filtered down to Products whose Name start with the lettter P?

Answer 1

I would go about this with a DetachedCriteria:

DetachedCriteria crit = DetachedCriteria.For<Order>();

crit.Add(Restrictions.Eq("OrderId",orderID);
crit.CreateCriteria("Products","products");
crit.Add(Restrictions.Like("products.ProductName","P%");

crit.List();

and then executing the criteria and getting the results.