Nhibernate subquery with multiple columns join

Question

I have database structure for Plans and PlanVersions in following relationship:

 +------+                  +-------------+
 | Plan |  --------------> | PlanVersion |
 +------+  1        (1..n) +-------------+

PlanVersion is version table tracking all version changes and it have ActiveFromData and ActiveToData columns show us when was this version active. Plan also can have SubPlans which can change in time so PlanVersion also have ParrentPlanId column which tell us what was current subplan for version.

What i want is to get all changes of all SubPlans since some time and for specific Plan. This query is what i came with:

DECLARE @since datetime;
set @since = '2014-08-27 12:00:00.000';

DECLARE @parrentPlan bigint;
set @parrentPlan = 1;

SELECT pv.* 
FROM [dbo].[PlanVersion] pv
INNER JOIN
    /* Query Over subselect*/
    (
       SELECT PlanId, MAX(ActiveFromDate) AS MaxActiveFromDate
       FROM [dbo].[PlanVersion] pv 
       WHERE pv.ParentPlanId=@parrentPlan
       GROUP BY PlanId
    ) groupedVersions
ON pv.ParentPlanId = groupedVersions.PlanId 
    AND pv.ActiveFromDate = groupedVersions.MaxActiveFromDate
WHERE (pv.ActiveFromDate>=@since OR pv.ActiveToDate>@since)

Now i want is translate that to Nhibernate QueryOver: i have this code

var subquery = QueryOver.Of<PlanVersion>()
                    .Where(x => x.ParentPlan.Id == parrentPlanId)
                    .Select(
                         Projections.Group<PlanVersion>(e => e.ParrentPlan.Id),
                         Projections.Max<PlanVersion>(e => e.ActiveFromDate)
                    );

But i dont know how to write that inner join on Two columns from suquery in QueryOver.

Notes:

We use Nhibernate 3.3 with 4.0 in testing
This query will be part of polling so performance is really important for me

You can't join on a derived table with QueryOver. You must join on mapped entity associations. — Andrew Whitaker, Aug 27 '14 at 15:29
Thanks for reply but thats a huge disapointment for NHibernate. I know i can do QueryOver.WithSubquery.WhereProperty(pv=>pv.ParrentPlan.Id).In(subquery) but that only works if i select IDs in subquery. Is there any solution or alternative for that in Nhibernate? They have to solve problems like this in mature ORM framework like NHibernate. — LightCZ, Aug 27 '14 at 15:34
Unfortunately you don't have a lot of options in the NH world. You could always write a stored procedure and drop back into that for this particular query — Andrew Whitaker, Aug 27 '14 at 15:39
If performance is your main concern - I would stick with raw sql (use a named sql-query). I never get the aversion to using the right tool for the right job...NHibernate gives tons of flexibility in this respect. — DanP, Aug 28 '14 at 13:39
Its really a disappointment that we cannot use subquery as derived table. My boss said no to stored procs. I need to return 4 last vendors on max(transactiondate) for each product. Found no solution anywhere since a month. If you guys have any idea, plz help. — Builder, Mar 18 '15 at 02:20

score 1 · Accepted Answer · edited Sep 18 '17 at 10:46

I would say, that this has solution. We have to use a bit more complex SQL in fact. This approach I've already deeply explained here:

Query on HasMany reference

So, below is just a draft based on your subquery draft. What we are doing, is creating two subselects in fact (check the intended SQL here)

PlanVersion planVersion = null;

// the most INNER SELECT
var maxSubquery = QueryOver.Of<PlanVersion>()
   .SelectList(l => l
    .SelectGroup(item => item.ParentPlan.Id)
    .SelectMax(item => item.ActiveFromDate)
    )
    // WHERE Clause
   .Where(item => item.ParentPlan.Id == planVersion.ParentPlan.Id)
   // HAVING Clause
   .Where(Restrictions.EqProperty(
      Projections.Max<PlanVersion>(item => item.ActiveFromDate),
      Projections.Property(() => planVersion.ActiveFromDate)
    ));

// the middle SELECT
var successSubquery = QueryOver.Of<PlanVersion>(() => planVersion )
    // the Plan ID
    .Select(pv => pv.ParentPlan.Id)
    .WithSubquery
    .WhereExists(maxSubquery)
    ;

having this subqueries, we can ask for plan itself:

// the most outer SELECT
var query = session.QueryOver<Plan>()
    .WithSubquery
    .WhereProperty(p => p.Id)
    .In(successSubquery)
    .List<Plan>();

There could be some minor typos, but the draft should give you clear answer how to...

Amazing to see that sir! Enjoy NHibernate, great tool ;) – Radim Köhler Aug 29 '14 at 11:16 — Radim Köhler, Aug 29 '14 at 11:16

Nhibernate subquery with multiple columns join

1 Answers1

Linked