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

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.