在NHibernate中，使用Disjunction可得出双重结果 [英] 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)) )

我得到了想要的结果.

但是，如果我像这样使用Disjunction:

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

真正的解决方案如 RadimKö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'

此查询的结果是什么?

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中将是这样

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中，使用Disjunction可得出双重结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！