NHibernate QueryOver中的子查询父id
本文关键字:查询 id QueryOver NHibernate | 更新日期: 2023-09-27 18:17:55
我试图理解这可能是如何工作的。我想要的是给定id列表中所有没有腐烂苹果的树。看起来很容易,但我是新鲜的NHibernate,不太好在SQL,你可以看到我卡住了。
我在下面写了这段代码:
Tree treeitem = null;
QueryOver<Apple> qapple = QueryOver.Of<Apple>()
.Where(x => (!x.IsRotten))
.And(Restrictions.IdEq(Projections.Property<Tree>(y => y.Id)))
// Or this one...
//.And(Restrictions.EqProperty(
// Projections.Property<Apple>(y => y.Tree.Id),
// Projections.Property<Tree>(y => y.Id)))
.Select(x => x.Id);
return this.NHibernateSession.QueryOver<Tree>()
.Where(x => x.Id.IsIn(ListOfTreeId))
.WithSubquery.WhereExists<Apple>(qapple)
.SelectList(list => list
.Select(z => z.Id).WithAlias(() => treeitem.Id)
.Select(z => z.Name).WithAlias(() => treeitem.Name)
.Select(z => z.Type).WithAlias(() => critem.Type)
.TransformUsing(Transformers.AliasToBean<Tree>())
.List<T>();
我得到的伪SQL是这样的:
SELECT id, name, type FROM trees WHERE id IN (1, 2, 3)
AND EXIST(SELECT id FROM apples WHERE NOT rotten AND apples.idtree = apples.id)
正如你所看到的,使用相同表Id的子查询有一个问题,而不是像这样:
EXIST(SELECT id FROM apples WHERE NOT rotten AND apples.idtree = tree.id)
实际上我有点迷路了。也许还有另一种方法来建立它。欢迎任何帮助,谢谢。
我不知道为什么当返回类型与查询类型相同时使用resulttransformer
return NHibernateSession.QueryOver<Tree>()
.Where(t => t.Id.IsIn(ListOfTreeId))
.JoinQueryOver<Apple>(t => t.Apples)
.Where(a => !a.IsRotten)
.List();
更新:编译器选择ICollection<Apple>
,而它实际上应该选择Apple
,因此在JoinQueryOver中显式指定泛型参数
Update2:获取唯一
选择1)
...
.SetResultTransformer(Transformers.DistinctRootEntity());
.List();
选择2)
Tree treeAlias = null;
var nonRottenApples = QueryOver.Of<Apple>()
.Where(a => !a.IsRotten)
.Where(a => a.Tree.Id == treeAlias.Id)
.Select(x => x.Id); <- optional
return NHibernateSession.QueryOver(() => treeAlias)
.Where(t => t.Id.IsIn(ListOfTreeId))
.WithSubquery.WhereExists(nonRottenApples)
.List();