Return class from a 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. makes a selection for all tables, not just products 2. makes left outer joins, not internal 3. Distinct () does not work in the query (although ToList ().) Does.)

It can also be done using SelectMany (a => a.Products). Select Many (ap => ap.Category.Products), but it does not work at all with the current NHibernate.Linq.

So, I want to use ICriteria. But I don’t see how to return the product, not the 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 get back somehow? ("p")? So that i can do

return criteria.List<Product>();

what will fail because ICriteria chooses actions rather than products?

HQL, ... , HQL, SQL, :

 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

- ( , CreateAlias ​​ 1 ),

 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>();

, "SELECT FROM products WHERE id in (subquery)", ( DISTINCT), , . , API , . , :

• HQL
• API , .
• NH-Linq, , .

, , HQL, Linq .

Answer 2

Projections, - :

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>();

nhibernate docs .

Answer 3

, ... , , , :

 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")));

, NHibernate , . , SQL. ,

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

. , - unit test , Product.