using nHibernate QueryOver to join a subset

Question

I am using nHibernate for our database access. I need to do a complicated query to find all member journal entries after a certain date with certain value, PreviousId, set for each member. I can easily write the SQL for it:

SELECT J.MemberId, J.PreviousId
FROM tblMemMemberStatusJournal J 
INNER JOIN (
    SELECT MemberId,
        MIN(EffectiveDate) AS EffectiveDate
    FROM tblMemMemberStatusJournal 
    WHERE EffectiveDate > @StartOfMonth
        AND (PreviousId is NOT null)
    GROUP BY MemberId
) AS X ON (X.EffectiveDate = J.EffectiveDate AND X.MemberId = J.MemberId)

However I am having a lot of trouble trying to get nHibernate to generate this information. There is not a lot of (any) documentation for how to use QueryOver.

I have been seeing information in other places, but none of it is very clear and very little has an actual explanation as to why things are done in certain ways. The answer for Selecting on Sub Queries in NHibernate with Critieria API did not give an adequate example as to what it is doing, so I haven't been able to replicate it.

I've gotten the inner part of the query created with this:

IList<object[]> result = session.QueryOver<MemberStatusJournal>()
        .SelectList(list => list
            .SelectGroup(a => a.Member.ID)
            .SelectMin(a => a.EffectiveDate))
        .Where(j => (j.EffectiveDate > firstOfMonth) && (j.PreviousId != null))
        .List<object[]>();

Which, according to the profiler, makes this SQL:

SELECT this_.MemberId           as y0_,
   min(this_.EffectiveDate) as y1_
FROM   tblMemMemberStatusJournal this_
WHERE  (this_.EffectiveDate > '2014-08-01T00:00:00' /* @p0 */
    and not (this_.PreviousLocalId is null))
GROUP  BY this_.MemberId

But I am not finding a good example of how to actually do join this subset with a parent query. Does anyone have any suggestions?

Answer 1

You aren't actually joining on a subset, you're filtering on a subset. Knowing this, you have the option of filtering via other means, in this case, a correlated subquery.

The solution below first creates a detatched query to act as the inner subquery. We can correlate properties of the inner query with properties of the outer query through the use of an alias.

MemberStatusJournal memberStatusJournalAlias = null; // This will represent the 
                                                     // object of the outer query

var subQuery = QueryOver.Of<MemberStatusJournal>()
                  .Select(Projections.GroupProperty(Projections.Property<MemberStatusJournal>(m => m.Member.ID)))
                  .Where(j => (j.EffectiveDate > firstOfMonth) && (j.PreviousId != null))
                  .Where(Restrictions.EqProperty(
                             Projections.Min<MemberStatusJournal>(j => j.EffectiveDate),
                             Projections.Property(() => memberStatusJournalAlias.EffectiveDate)
                         )
                        )
                  .Where(Restrictions.EqProperty(
                            Projections.GroupProperty(Projections.Property<MemberStatusJournal>(m => m.Member.Id)),
                            Projections.Property(() => memberStatusJournalAlias.Member.Id)
                       ));

var results = session.QueryOver<MemberStatusJournal>(() => memberStatusJournalAlias)
                     .WithSubquery
                     .WhereExists(subQuery)
                     .List();

This would produce an SQL query like the following:

SELECT blah
FROM tblMemMemberStatusJournal J 
WHERE EXISTS (
    SELECT J2.MemberId
     FROM tblMemberStatusJournal J2
    WHERE J2.EffectiveDate > @StartOfMonth
        AND (J2.PreviousId is NOT null)
    GROUP BY J2.MemberId
    HAVING MIN(J2.EffectiveDate) = J.EffectiveDate
    AND J2.MemberId = J.MemberId
)

This looks less efficient than the inner join query you opened the question with. But my experience is that the SQL Query Optimizer is clever enough to convert this into an inner join. If you want to confirm this, you can use SQL Studio to generate and compare the execution plans of both queries.