问题描述
我已经搜索了几个小时了,但是似乎找不到任何可以帮助我的东西.
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与子查询联接以获取聚合列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!