具有左外连接的复杂 NHibernate 条件

Question

我对NHibernate很陌生。我使用CreateSQLQuery编写了这篇文章，但如果可能的话，我想将其移至NHibernate标准格式。我的查询如下所示：

select parent.ID as Id, ValueA.Total as ValueACount, ValueB.Total as ValueBCount
from ParentTable parent 
left outer join
(
  select count(*) as Total, ID 
  from ChildTable
  where state = 'ValueA' 
  group by ID
) ValueA on ValueA.ID = parent.ID
left outer join
(
  select count(*) as Total, ID
  from ChildTable
  where state = 'ValueB' 
  group by ID
) ValueB on ValueB.ID = parent.ID

我更改了表名称/值以对其进行抽象。代码按原样工作，但这是我们在整个解决方案中唯一的查询。我想看看我们是否可以摆脱它。

提前感谢所有可以提供帮助的人。如果你想给我一个非常好的网页的链接，可以帮助我，那也没关系。我至少会为你竖起大拇指:)

我也看到过一些类似的问题。如果您觉得其他一些问题/答案会对我有很大帮助，请随时指出我。

Answer 1

你可以尝试一些linq fu

var results = from p in session.Query<Parent>()
              select new
              {
                  p.Id,
                  ValueACount = (from c1 in session.Query<Child>() where c1.State == "ValueA" && c1.Parent == p select c1).Count(),
                  ValueBCount = (from c2 in session.Query<Child>() where c2.State == "ValueB" && c2.Parent == p select c2).Count(),
              };

或使用条件

var results = session.CreateCriteria<Parent>("p")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("Id"))
        .Add(Projections.SubQuery(DetachedCriteria.For<Child>()
            .Add(Restrictions.Eq("State", "ValueA") && Restrictions.EqProperty("Parent", "p"))
            .SetProjection(Projections.RowCount())))
        .Add(Projections.SubQuery(DetachedCriteria.For<Child>()
            .Add(Restrictions.Eq("State", "ValueB") && Restrictions.EqProperty("Parent", "p"))
            .SetProjection(Projections.RowCount()))))
    .List();