使用NHibernate从嵌套集合返回类 [英] Return class from nested collection using NHibernate

问题描述

杜曼:

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

现在，我想要这个:

 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;

此Linq实际上有效，但:1.对所有表而不是仅对Products产生select.2.产生左外部联接，而不是内部联接.3.查询上的Distinct()不起作用(尽管ToList().Distinct( ).)

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).

也可以使用SelectMany(a => a.Products).SelectMany(ap => ap.Category.Products)来完成，但对于当前的NHibernate.Linq根本不起作用.

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.

所以我想使用ICriteria.但是我看不到如何退货，而不是退货?

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

那么我该如何SomeReturnMeOnly("p")?这样我就可以

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

return criteria.List<Product>();

由于ICriteria选择操作而不是产品，这会失败吗?

which will fail because ICriteria selects Actions, not Products?

我可能考虑使用HQL，但实际上我不喜欢字符串查询...例如，这是可以正常工作并产生所需的SQL的HQL:

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

推荐答案

现在，可以使用

 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产品，ID在(子查询中)"可能会更好(不需要DISTINCT)，但这不是我想要实现的.似乎Criteria API非常非常严格.因此，我们有:

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
2. 具有很多限制的标准API，有时还需编写糟糕的代码才能获得简单的结果
3. NH-Linq看起来很有希望，但现在还不完善.

所以我想我会坚持使用HQL，直到Linq准备好为止.

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

这篇关于使用NHibernate从嵌套集合返回类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！