In NHibernate, using a Disjunction gives double results

Question

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

If I use:

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

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

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

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?

UPDATE:

For the DISTINCT part:

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

or

Projections.Distinct(Projections.Id())

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

Answer 1

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

Why recieving not distinct?

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

Parent:

ParentId | Name
1        | Parent_A
2        | Parent_B

Child:

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

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

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)

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.

Solution

The solution is in the subselect

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

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

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