Fluent NHibernate QueryOver 选择不在另一个表中的项目(左连接) [英] Fluent NHibernate QueryOver to select items not in another table (left join)

问题描述

我有两张桌子:

全部:编号 |道具A |道具B |其他列

all: id | propA | propB | someOtherColumn

隐藏:编号 |道具A |道具B

hidden: id | propA | propB

和相应的类(已映射，尚未映射关系)

and corresponding classes (Mapped, no relationship mapped yet)

我想从第一个表中获取所有行，减去与 propA 或 propB 属性匹配的任何结果.

I would like to get all rows from first table, minus any results which match by propA or propB property.

我设法通过 Criteria API 做到了这一点，但想看看它是如何使用 QueryOver API 完成的，如果可能的话没有子查询但有一个左排除连接.

I managed to do it by Criteria API but would like to see how it is done with QueryOver API, if possible without subqueries but with a left excluding join.

标准版本:

var dc1 = DetachedCriteria.For<hidden>()
    .Add(Restrictions.IsNotNull(Projections.Property("propA")))
    .SetProjection(Projections.Property("propA"));

var dc2 = DetachedCriteria.For<hidden>()
    .Add(Restrictions.IsNotNull(Projections.Property("propB")))
    .SetProjection(Projections.Property("propB"));



var query = db
    .CreateCriteria<all>()
    .Add(Restrictions.On<all>(c => c.someOtherColumn).IsLike("1"))
    .Add(Subqueries.PropertyNotIn("propA", dc1))
    .Add(Subqueries.PropertyNotIn("propB", dc2))

大致给出:

SELECT all.* 
FROM all
WHERE (all.someOtherColumn LIKE '1')
    and all.propA not in (SELECT hidden.propA FROM hidden WHERE hidden.propA IS NOT NULL) 
    and all.propB not in (SELECT hidden.propB FROM hidden WHERE hidden.propB IS NOT NULL)

没关系，虽然从性能上看这会更好:

It is ok, though it seems performance wise this would be better:

SELECT all.* 
FROM all
LEFT JOIN hidden ON all.propA = hidden.propA
LEFT JOIN hidden ON all.propB = hidden.propB
WHERE hidden.propA IS NULL
AND hidden.propB IS NULL 
AND (all.someOtherColumn LIKE '1')

如果无法使用未映射的关系生成这样的语句，我愿意接受有关映射的建议.

If it is not possible to generate such a statement with unmapped relationships, I am open to suggestions on mapping.

推荐答案

在我看来，在没有映射关系的情况下通过 QueryOver 创建连接是不可能的.请参阅此答案.

It seems to me that it's not possible to create joins without mapped relationships via QueryOver. See this answer.

使用子查询，您可以执行以下操作:

With subqueries you can do something like this:

All allAlias = null;
var result = Session.QueryOver(() => allAlias)
               .WhereRestrictionOn(x => x.someOtherColumn).IsLike('1')
               .WithSubquery.WhereNotExists(QueryOver.Of<hidden>()
                     .Where(h => h.propA == allAlias.propB || h.propB == allAlias.propB)
                     .Select(h => h.Id))
               .List();

这篇关于Fluent NHibernate QueryOver 选择不在另一个表中的项目(左连接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！