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
– Andrew Whitaker, Commented Aug 27, 2014 at 15:29
Thanks for reply but thats a huge disapointment for NHibernate. I know i can do QueryOver<PlanVersion>.WithSubquery.WhereProperty<PlanVersion>(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
– LightCZ, Commented Aug 27, 2014 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
– Andrew Whitaker, Commented Aug 27, 2014 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
– DanP, Commented Aug 28, 2014 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
– Builder, Commented Mar 18, 2015 at 2:20

marc_s · Accepted Answer · 2017-09-18 10:46:40Z

1

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

edited Sep 18, 2017 at 10:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 27, 2014 at 15:59

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Radim Köhler Over a year ago

Amazing to see that sir! Enjoy NHibernate, great tool ;)

Collectives™ on Stack Overflow

Nhibernate subquery with multiple columns join

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related