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!