在 NHibernate 中，使用析取会产生双重结果 [英] In NHibernate, using a Disjunction gives double results

问题描述

我正在尝试使用 DetachedCriteria 进行选择，我想在运行时添加几个由 OR 分隔的条件.

I'm trying to make a select with DetachedCriteria, I want to add several conditions seperated by OR at runtime.

如果我使用:

Restrictions.Or( cond1, Restrictions.Or(cond2, Restrictions.Or(cond3, cond4)) )

我得到了我想要的结果.

I get the result I want.

但是如果我像这样使用析取:

But if I use a Disjunction like so:

var disjunction = Restrictions.Disjunction();
disjunction.Add(cond1);
disjunction.Add(cond2);
disjunction.Add(cond3);
disjunction.Add(cond4);

我有实体 cond1 和 cond2 对他们来说是真的，在结果中我得到了两次(在列表结果中两次返回相同的实体).

And I have entities that cond1 and cond2 are true for them, at the results I get them twice (the same exact entity is returned twice in the list result).

我不想使用 QueryOver，因为我试图完成一些用 QueryOver 很难做到的事情(我试图做的最终结果是从过滤器的 json 中获取 sql 查询).

I do not wish to use QueryOver because I'm trying to accomplish something that is hard to do with QueryOver (the end result of what I'm trying to do is to get an sql query from a json of filters).

是什么导致析取返回双打?有没有办法在最后添加一个 DISTINCT ?我做错了吗，我不应该对同一张桌子上的不同条件使用析取?

What's causing the disjunction to return doubles? Is there a way to add a DISTINCT at the end? Am I doing it wrong and I shouldn't use disjunction for different conditions on the same table?

更新:

对于 DISTINCT 部分:

For the DISTINCT part:

criteria.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer());

或

Projections.Distinct(Projections.Id())

真正的解决方案如Radim Köhler所述——正确使用子查询.

The real solution is as stated by Radim Köhler - The correct use of a sub query.

推荐答案

一个小借口:问题没有提供任何映射，也缺少查询......所以只能猜测是什么问题.但是让我们尝试提供一些解释

A little excuse: the question does not provide any mapping, there is also missing the query... So one can only guess what's the problem. But let's try to provide some explanation

让我们有两个表格(如问题下方的评论之一所示)

Let's have two tables (as given in one of the comments below the question)

家长:

ParentId | Name
1        | Parent_A
2        | Parent_B

孩子:

ChildId | Color | ParentId
1       | green | 1
2       | grey  | 1
3       | gold  | 1
4       | green | 2

如果我们将在纯 SQL 中创建简单的选择，就拥有这个

Having this if we will create the simple selection in a pure SQL

SELECT p.ParentId, p.Name
FROM Parent AS p
  INNER JOIN Child AS c
    ON p.ParentId = c.ParentId
WHERE 
  c.Color = 'green' OR c.Color = 'grey' OR c.Color = 'gold'

这个查询的结果是什么?

what will be the result of this query?

1 | Parent_A
1 | Parent_A
1 | Parent_A
2 | Parent_B

如果我们将其转换为类似的标准:

If we will convert it into similar criteria:

var sesion = ... // get session 

var parent = sesion.CreateCriteria<Parent>();

var children = parent.CreateCriteria("Children");

// restrict the children
children.Add(Restrictions.Disjunction()
    .Add(Restrictions.Eq("Color", "green"))
    .Add(Restrictions.Eq("Color", "grey"))
    .Add(Restrictions.Eq("Color", "gold"))
    );

var list = parent
    .SetMaxResults(10) // does not matter in our example, but ... it should be used always
    .List<Parent>();

这是 Criteria C# 代码，这将导致多个父项 (因为事实上，将如上所述生成相同的 SQL)

And this is the Criteria C# code, which will result in a multiple Parents (because of the fact, that the same SQL will be generated as stated above)

正如我们所看到的，问题绝对不是在 NHiberante 方面.真的！NHibernate 不仅是无辜的，而且还做应有的事.

As we can see, the problem is definitely not on the NHiberante side. Really! NHibernate is not only innocent, but also doing what was required.

解决方案在子选择中

在 SQL 中会是这样的

In the SQL it will be like this

SELECT p.ParentId, p.Name
FROM Parent AS p
WHERE p.ParentId IN (
  SELECT c.ParentId
  FROM Child AS c
    WHERE c.ParentId = p.ParentId
    AND c.Color = 'green' OR c.Color = 'grey' OR c.Color = 'gold'
)

这将为我们提供我们最可能想要的结果:

That will provide us with the result we most likely want:

1 | Parent_A
2 | Parent_B

如何在 NHibernate 中做到这一点?

And how to do that in NHibernate?

var sesion = ... // get session 

var parent = sesion.CreateCriteria<Parent>();

//var children = parent.CreateCriteria("Children");
var children = DetachedCriteria.For(typeof(Child));

// restrict the children
children.Add(Restrictions.Disjunction()
    .Add(Restrictions.Eq("Color", "green"))
    .Add(Restrictions.Eq("Color", "grey"))
    .Add(Restrictions.Eq("Color", "gold"))
    );

// ad SELECT into this sub-select
children.SetProjection( Projections.Property("ParentId"));

// filter the parent
parent
    .Add(Subqueries.PropertyIn("ParentId", children));


var list = parent
    .SetMaxResults(10) // does not matter in our example, but ... it should be used always
    .List<Parent>();

现在，我们确实有子选择(DetachedCriteria 和 Subqueries NHibernate 功能)并且不再有重复项！

Now, we do have sub-select (DetachedCriteria and Subqueries NHibernate features) and no more DUPLICATES!

这篇关于在 NHibernate 中，使用析取会产生双重结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！