nhibernate queryover join with subquery to get aggregate column

Question

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

Here is the database model:

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.

Branch model

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 model

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

    public virtual DateTime AssignedOn { get; set; }

    public virtual Branch Branch { get; set; }
}

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" />

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.

I tried like this:

 // 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...

Thank you, Cosmin

Tyrannize

Answer 1

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.

Tyrannize

nice indeed. I thought you need the whole Branch object and not only some properties. – Sommelier

I need it to populate a custom entity with some branch properties and the count of the assignments. Nevertheless, thank you for taking the time to reply, I think your answer is interesting also. – Tyrannize

This creates a nested subquery rather than a left join. I would expect this to perform a lot worse than a join (though the SQL Query optimizer continually surprises me :-) – Chucklehead

I ran two queries in SQL Server and looked at the execution plan. The query engine gets a correlated subquery and converts it into a right outer join. Which means the performance would be identical either way. – Chucklehead

@AndrewShepherd I know you did this analysis a longtime ago, but thank you for doing so. I was reading through this solution and had similar concerns. Your work assuaged those fears. Thank you! – Digamy

Answer 2

its not that easy with QueryOver, because it is currently not possible to have statements in the FROM clause. One thing that comes to my mind (not the most efficient way i think)

var branches = session.QueryOver<Branch>().Future();

var assignmentMap = session.QueryOver<BranchAssignment>()
    .Select(
        Projections.Group<BranchAssignment>(ab => ab.Branch.Id).As("UserId"),
        Projections.RowCount())
    .Future<object[]>()
    .ToDictionary(o => (int)o[0], o => (int)o[1]);

return branches.Select(b => new { Branch = branch, AssignmentCount = assignmentMap[branch.Id] });

with LINQ it would be

var branchesWithAssignementCount = session.Query<Branch>()
    .Select(b => new { Branch = b, AssignmentCount = b.Branch.Count })
    .ToList();

Sommelier