Return class from nested collection using NHibernate

Question

Doman:

class Action
    Products: IList of class ActionProducts: 
          Category: class Category
                Products: IList of class Product

Now, I want this:

 var products = from a in Session.Linq<Action>()
                from ap in a.Products
                from p in ap.Category.Products
                where a.Name == name
                select p;

And this Linq actually works but: 1. produces select for all tables instead of only Products 2. produces left outer joins, not inner 3. Distinct() on the query doesn't work (though ToList().Distinct() works).

Also can be done with SelectMany(a => a.Products).SelectMany(ap => ap.Category.Products) but it doesn't work at all with current NHibernate.Linq.

So I want to use ICriteria. But I can't see how do I return product, not action?

 ICriteria criteria = Session.CreateCriteria(typeof(Action))
    .Add(Expression.Eq("Name", name))
    .CreateAlias("Products", "ap")
    .CreateAlias("ap.Category.Products", "p")
    .SomehowReturnMeOnly("p");

So how do I SomehowReturnMeOnly("p")? So that I can do

return criteria.List<Product>();

which will fail because ICriteria selects Actions, not Products?

I may consider HQL but I actually doesn't like string queries... Just for example, here's the HQL that works and produces exactly the SQL that I need:

 IQuery query = Session.CreateQuery("select distinct p from Action a inner join a.Products as ap inner join ap.Category.Products as p");
 return query.List<Product>();

Answer 1

You need to use Projections, something like this:

ICriteria criteria = Session.CreateCriteria(typeof(Action))
    .Add(Expression.Eq("Name", name))
    .CreateAlias("Products", "ap")
    .CreateAlias("ap.Category.Products", "p")
    .SetProjection(Projections.Property("ap.Category.Products"))
    .List<Product>();

Have a look at the nhibernate docs here for some examples.

Answer 2

Well, after thinking about Chris' answer... I tried this and it seemed to work:

 ICriteria criteria = Session.CreateCriteria(typeof(Action))
    .Add(Expression.Eq("Name", name))
    .CreateAlias("Products", "ap")
    .CreateAlias("ap.Category", "c")
    .SetProjection(Projections.Distinct(Projections.Property("c.Products")));

Looks like NHibernate doesn't allow deep nesting of projection properties which is strange. And it doesn't work either, looking at generated SQL I see that it only selects

SELECT distinct c2_.Id as y0_ FROM ... Categories c2_ ...

i.e. it doesn't really fetch products, which makes my unit test to fail because returned list contains only nulls instead of Product instances.

Answer 3

Now, something similar can be done (keeping in mind that CreateAlias can only do 1 level) using

 DetachedCriteria dq = DetachedCriteria.For<Action>()
     .Add(Expression.Eq("Name", name))
     .CreateAlias("Products", "ap")
     .CreateAlias("ap.Category", "c")
     .CreateAlias("c.Products", "p")
     .SetProjection(Projections.Property("p.Id"));
  ICriteria criteria = Session.CreateCriteria(typeof(Product))
     .Add(Subqueries.PropertyIn("Id", dq));
  return criteria.List<Product>();

This works and passes test, but produces "SELECT FROM products WHERE id in (subquery)" which may be even better (no DISTINCT required) but is not what I wanted to achieve. Seems like Criteria API is very, very restrictive. So we have:

1. HQL with string-query drawbacks
2. Criteria API with lots of restrictions and sometimes awful code to achieve simple results
3. NH-Linq which looks very promising but is incomplete now.

So I guess I'll stick with HQL until Linq is ready.