nhibernate queryover 与子查询连接以获取聚合列 [英] nhibernate queryover join with subquery to get aggregate column

问题描述

我已经搜索了几个小时如何做到这一点，但似乎找不到任何可以帮助我的东西.

I have been searching for several hours now how to do this, but can't seem to find anything to help me.

这是数据库模型:

这是我尝试运行的 SQL 查询:

This is the SQL query I am trying to run:

SELECT b.*, a.Assignments FROM Branch b LEFT JOIN (
        SELECT b.BranchID , COUNT(ab.BranchID) AS Assignments
        FROM Branch b LEFT JOIN AssignmentBranch ab ON b.BranchID = ab.BranchID
        GROUP BY b.BranchID
      ) a ON b.BranchID = a.BranchID

所以，基本上，我想返回一个分支列表和一个表示该分支分配数量的新列.

So, basically, I want to return a list of branches and a new column that represents the number of assignments for that branch.

分支模型

public class Branch : IEntity<int>
{
    public virtual int ID
    {
        get;
        set;
    }

    public virtual string Name { get; set; }

    public virtual IList<AssignmentBranch> Assignments { get; set; }

}

AssignmentBranch 模型

AssignmentBranch model

public class AssignmentBranch : IEntity<int>
{
    public virtual int ID
    {
        get;
        set;
    }

    public virtual DateTime AssignedOn { get; set; }

    public virtual Branch Branch { get; set; }
}

这是我的 NHibernate 配置:

Here is my NHibernate configuration:

<class name="Branch" table="Branch">

<id name="ID" column="BranchID">
  <generator class="identity"></generator>
</id>

<property name="Name"/>

<bag name="Assignments" cascade="none" inverse="true">
  <key column="BranchID"/>
  <one-to-many class="AssignmentBranch"/>
</bag>

 <class name="AssignmentBranch" table="AssignmentBranch">

<id name="ID" column="AssignmentBranchID">
  <generator class="identity"></generator>
</id>

<property name="AssignedOn" />
<property name="FromDate" />
<property name="ToDate" />

<many-to-one name="Assignment" column="AssignmentID" />
<many-to-one name="Branch" column="BranchID" />

我已经尝试了多种方法，但似乎找不到使用 QueryOver 加入子查询的方法.

I have tried this a number of ways, but I can't seem to find a way to join with a sub-query using QueryOver.

我试过这样:

 // aliases
 Branch branch = null; AssignmentBranch assignment = null;

 var subquery = QueryOver.Of<Branch>(() => branch)
     .Where(() => branch.Project.ID == projectID)
     .JoinQueryOver<AssignmentBranch>(() => branch.Assignments, ()=> assignment, 
                                   NHibernate.SqlCommand.JoinType.LeftOuterJoin)
     .SelectList(list => list
                        .SelectGroup(x=>x.ID)
                        .SelectCount(()=>assignment.ID)
                    );

     var query = session.QueryOver<Branch>(()=>branch)
                  .JoinAlias(???) // how can I join with a sub-query?
                  .TransformUsing(Transformers.AliasToBean<BranchAssignments>())
                  .List<BranchAssignments>();

谁能帮帮我?它不一定与子连接完全一致，也许还有另一个更好的解决方案我错过了......

Can anyone help me please? It doesn't have to be with a sub-join exactly, maybe there is another better solution out there that I am missing...

谢谢你，科斯敏

推荐答案

在这里阅读了数百个类似的问题后，我找到了答案:相关子查询.像这样:

After reading hundreds of similar questions in here, I have found the answer: a correlated sub-query. Like this:

// aliases
Branch branch = null; AssignmentBranch assignment = null;

var subquery = QueryOver.Of<AssignmentBranch>(() => assignment)
    .Where(() => assignment.Branch.ID == branch.ID)
    .ToRowCountQuery();

var query = session.QueryOver<Branch>(() => branch)
     .Where(() => branch.Project.ID == projectID)
     .SelectList
     (
         list => list
         .Select(b => b.ID)
         .Select(b => b.Name)
         .SelectSubQuery(subquery)
     )
     .TransformUsing(Transformers.AliasToBean<BranchAssignments>())
     .List<BranchAssignments>();

我得到答案的类似问题是 这个.

The similar question I got my answer from is this one.

这篇关于nhibernate queryover 与子查询连接以获取聚合列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！