具有多列连接的 Nhibernate 子查询 [英] Nhibernate subquery with multiple columns join

问题描述

我有以下关系的计划和计划版本的数据库结构:

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

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

PlanVersion 是跟踪所有版本更改的版本表，它有 ActiveFromData 和 ActiveToData 列向我们显示此版本何时处于活动状态.计划也可以有可以及时更改的子计划，所以 PlanVersion 也有 ParrentPlanId 列，它告诉我们版本的当前子计划是什么.

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) 

现在我想把它翻译成 Nhibernate QueryOver:我有这个代码

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)
                    );

但我不知道如何在 QueryOver 中的 suquery 的两列上编写内部连接.

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

注意事项:

1. 我们在测试中使用 Nhibernate 3.3 和 4.0
2. 此查询将成为轮询的一部分，因此性能对我来说非常重要

推荐答案

我想说，这有解决方案.事实上，我们必须使用更复杂的 SQL.我已经在这里深入解释了这种方法:

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:

• 查询 HasMany 参考

因此，以下只是基于您的子查询草稿的草稿.我们正在做的实际上是创建两个子选择(检查预期的 SQL 此处)

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

这篇关于具有多列连接的 Nhibernate 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！