Hey all,

Got a query about a query :). I have a structure like so:

Product Groups
Product Categories
Products

A Product group has many categories and a category many products. I am trying to select a list of groups where products match a specific criteria. This query works for me:

var groups = session.CreateCriteria(typeof (Model.ProductGroup))
.CreateCriteria("ProductCategories")
.CreateCriteria("Products", "p")
.Add(new GtExpression("Nitrogen", 10D))
.List <Model.ProductGroup>();


The issue is I would like the producs to be filtered by this criteria, at the moment all products will be returned below a groups categories, products collection. Am I trying to do something that isnt possible or is there a solution?



Any help would be appreciated.



Cheers
Stefan

Is this just me being stupid? I am starting to think what I am trying to do might not work?

Anybody got any ideas for me?

You need to navigating the relationships between your entities:


If you are using field mappings for your collections then you'll need to refer to the field because NHibernate does not know about the properties:

Thanks, I have worked this out, the trick was create alias and joins:

.CreateAlias("ProductCategories", "pc", JoinType.InnerJoin)
.CreateAlias("pc.Products", "p", JoinType.LeftOuterJoin)

Then set the result transformer to be unique:

.SetResultTransformer(new DistinctRootEntityResultTransformer());

And it all works :). I am starting to like nHibernate now. Once I figure these things out.


Cheers
Stefan