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?

Damsel

Which did you use in the end? – Rapscallion

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.

Jefferyjeffie

Answer 2

Simplest approach is to use an alias:

var productIdToSelect = 9;
var crit = Session.CreateCriteria(typeof(Order));
crit.CreateAlias("Product", "prod");
crit.Add(Expression.Eq("prod.Id", productIdToSelect));
var result = crit.List<Order>();

Feldspar

Answer 3

I don't know the code you would have to write, but a point in the right direction:

http://www.nhforge.org/doc/nh/en/index.html#querycriteria-associations (14.4)

The key seems to be:

.SetResultTransformer(CriteriaUtil.AliasToEntityMap)

The documentation shows an example with cats and kittens.

Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use SetResultTransformer(CriteriaUtil.AliasToEntityMap).

Ratha

Thanks for your comment eyston, I had indeed followed the very same sample that you mention in your post. However using that code with the mysql dialect which is my use case gives an SQL not available error... I dont like to think that such a simple operation is not possible... There has to be something, if anyone could provide a working example that would be great! – Damsel

What version of NH are you running? – Onus

I have tried just that for a month or two ago with 2.0.1, nut never got it to work using the ResultTransformer. It is a bit vague to me now, but I recall, that I found it were a bug/missing feature, that might be solved/implmented in version 2.1.0. I may be able to look into it tomorrow at work, if its not too busy :) – Onus

Thanks for your reply asgerhallas - I will have to give it a spin with the new version too and see what i get out of it – Damsel

Answer 4

Set up a filter on the mapping of the collection.

<filter name="letterFilter" condition="ProductName like ':letterSupplied'"/>

Then before running the Order query enable the filter

session.EnableFilter("letterFilter").SetParameter("letterSupplied", "P%");

then run the query

Order ord = session.CreateCriteria<Order>().Add(Restrictions.IdEq(suppliedId)).UniqueResult<Order>();

Note that the single quotes in the filter definition may not be required and also i place the % symbol with the supplied parameter as i don't know how NH would react a filter like

<filter name="letterFilter" condition="ProductName like ':letterSupplied%'"/>

or

<filter name="letterFilter" condition="ProductName like :letterSupplied%"/>

Selfservice

VERY interesting approach - have not tried this one, got to test it, thanks Jaguar – Damsel