NHibernate criteria to query parent objects with at least one child object?
In my project, Lines
can be grouped and a Group
has a type which can be either Crossing
(1) or Parallel
(2). I need to find all lines which has at least one group of a 开发者_JAVA技巧specified type (in this case, 1). The Id of a given line can be either on column LineA
or LineB
of a group. Here is where i got so far:
Criteria crit = session.CreateCriteria(typeof(Line), "ln");
DetachedCriteria count = DetachedCriteria.For<Group>()
.SetProjection(Projections.CountDistinct("Id"))
.Add(Expression.Or(
Expression.EqProperty("LineA", "ln.Id"),
Expression.EqProperty("LineB", "ln.Id")))
.Add(Expression.Eq("GroupTypeId", 1));
crit.Add(Subqueries.Gt(0, count));
I got it working!
crit.Add(Expression.Sql(
"EXISTS(select 1 from Group" +
"WHERE ({alias}.Id=LineA OR {alias}.Id=LineB)"+
"AND GroupTypeId = ?)", (int) type, NHibernateUtil.Int32));
{alias}
is a placeholder for the object being queried.
精彩评论