NHibernate/QueryOver:如何离开带有参数的联接 [英] NHibernate / QueryOver: How to left join with parameter

问题描述

NHibernate/QueryOver API中是否有一种方法可以传递映射中已经存在的参数(因此它使用该参数作为此特定实例上所有查询的固定值).

Is there a way in NHibernate / QueryOver API to pass a parameter already in the mapping (so it uses the parameter as a fixed value for all queries on this particular instance).

我需要这个(或解决方法)，因为我在数据库中有一个这样的视图:

I need this (or a workaround) because I have a view like this in the Database:

CREATE VIEW ProblematicView
AS
SELECT
    v.*,
-- lots of data
FROM someView v
LEFT JOIN someTable t ON v.ForeignKey = t.ForeignKey

现在除了ForeignKey匹配之外，我还需要对这样的属性进行额外的检查:

Now additionally to the ForeignKey match I need an additional check for a property like this:

AND t.SomeOtherValue = @myParameter

这是不可能的，因为无法将参数直接传递给视图.使用表值函数，这是可能的，但是我不知道如何将其映射到NHibernate/QueryOver.

which is not possible as there is no way to pass parameters to a view directly. With a table valued function this would be possible but then I don´t know how to map it to NHibernate / QueryOver.

由于使用巨大的QueryOver语句过滤所有剩余属性(因为视图用于搜索业务实体)，因此功能方法也将难以实现

Also the function approach would be hard to realize as a huge QueryOver statement is used to filter all the remaining properties (as the view is used for searching business entities)

当前，我正在将SomeOtherValue/@myParameter过滤器应用于整个视图，并将其作为QueryOver的一部分.

Currently I am applying the SomeOtherValue / @myParameter filter to the the entire view as a part of my QueryOver.

这是我的主要问题:

使用例如:

SELECT
    v.*,
-- lots of data
FROM someView v
LEFT JOIN someTable t ON v.ForeignKey = t.ForeignKey AND t.SomeOtherValue = 123
 (followed by alot of other property checks...)

将返回不同的结果(由于左连接，意图中包含t.SomeOtherValue的NULL条目)

will return a different result (NULL entries for t.SomeOtherValue included on intent due to left join)

比使用:

SELECT * FROM ProblematicView where SomeOtherValue = 123
 (followed by alot of other property checks)

现在，左联接发生在视图内部，而无需检查SomeOtherValue，并且SomeOthervalue检查独立于左联接应用，因此将排除所有NULL值(这是错误的业务逻辑).

As now the left join happens inside the view without checking for SomeOtherValue and as the SomeOthervalue check is applied independent on the left join, all the NULL values will be excluded (which is wrong business logic).

也使用:

SELECT * FROM ProblematicView where SomeOtherValue = 123 OR SomeOtherValue = NULL
 (followed by alot of other property checks)

似乎没有帮助，因为仍然会忽略NULL值...

does not seem to help, as NULL values are still ignored...

所以我能想象到解决此问题的唯一方法是找到一种以某种方式将我的SomeOtherValue属性传递给视图的方法，以便它可以将其用作视图本身的参数(而不是where子句)或以某种方式使用基于sql表格的函数以及模型的参数...

So the only way I can imagine to solve this problem would be to find a way to pass my SomeOtherValue property somehow to the view so that it can use it as a parameter in the view itself (instead of in the where clause) or maybe somehow use an sql table based function with parameter for the model...

经过更多研究，我设法简化了这个问题:

After some more research I managed to hopefully simplify the problem:

我正在尝试转换此SQL:

I am trying to convert this SQL:

Select v.*, ... from (someView v LEFT JOIN someTable t ON v.ForeignKey = t.ForeignKey) 
WHERE SomeOtherValue = 123

(其中SomeOtherValue来自someOtherTable)

(where SomeOtherValue comes from someOtherTable)

对此:

Select v.*, ... from someView v LEFT JOIN someTable t on v.ForeignKey = t.ForeignKey
AND t.SomeOtherValue = 123

使用NHibernate/QueryOver.请注意，在第二个版本中，直接在左联接中检查属性SomeOthervalue的属性，在第一个版本中，仅在左联接之后才错误地应用了此属性.

using NHibernate / QueryOver. Note that in the second version the property SomeOthervalue is checked against directly within the left join, where in the first version it is incorrectly applied only after the left join.

我需要找到一种方法来编写后面的SQL语句，以便可以将其放入视图中，同时仍然能够将123作为SomeOtherValue的参数传递.

I need to find a way to write latter SQL statement in a way that I can put it inside a view while still being able to pass 123 as parameter for SomeOtherValue.

推荐答案

您可以使用带有withClause参数的JoinQueryOver或JoinAlias重载，将条件添加到join子句中.例如:

You can add a condition to a join clause using the overload of JoinQueryOver or JoinAlias that takes a withClause parameter. For example:

SomeTable stAlias = null;

session.QueryOver<ProblematicView>()
    .Left.JoinAlias(
        pv => pv.SomeTable,              // Join path
        () => stAlias,                   // alias assignment
        st => st.SomeOtherValue == 123)  // "with" clause
    /* etc. */

QueryOver联接的"with"部分将为SQL中的left outer join添加一个条件.上面应该生成这个:

The "with" portion of the QueryOver join is what will add a condition to the left outer join in SQL. The above should generate this:

SELECT /* select list */
FROM   [ProblematicView] this_
       left outer join [SomeTable] sometable1_
         on this_.Id = sometable1_.ProblematicViewId
            and (sometable1.SomeOtherValue = 123 /* @p0 */)

如果我的理解正确，那么应该可以帮助解决您的问题.

If I understand correctly, this should help solve your problem.

关于添加"with"子句的一些注意事项:

A few things to note about adding a "with" clause:

• 有趣的是，所有允许您指定"with"子句的JoinQueryOver和JoinAlias重载都要求在执行连接时分配别名.
• (据我所知)在联接条件下不能使用or生成SQL. "with"子句始终and具有映射的联接条件(即FK→PK)

• Interestingly, all of the overloads of JoinQueryOver and JoinAlias that allow you to specify a "with" clause require that you assign an alias when you do the join.
• You cannot (as far as I know) generate SQL with an or in the join condition. the "with" clause is always anded with the mapped join condition (i.e., FK → PK)

这篇关于NHibernate/QueryOver:如何离开带有参数的联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！