NHibernate Criteria question

Question

I have a person object, which can have unlimited number of first names. So the first names are another object.

ie

person --- name

            --- name

            --- name

What I want to do is write an nhiberate query using which will get me a person who has certain names.

so one query might be find someone whose names are alison and jane and philippa, then next query may be one to find someone whose names are alison and jane.

I only want to return people who have all the names I'm search on. So far I've got

ICriteria criteria = session.CreateCriteria(typeof (Person));
criteria.CreateAlias("Names", "name");
ICriterion expression = null;
foreach (string name in namesToFind)
{
    if (expression == null)
    {
        expression = Expression.Like("name.Value", "%" + name + "%");
    }
    else
    {
        expression = Expression.Or(
            expression,
            Expression.Like("name.Value", "%" + name + "%"));
    }
}

if (expression != null)
    criteria.Add(expression);

But this is returning every person with ANY of the names I'm searching on, not ALL the names.

Can anyone help me out with this? Thanks!

Answer 1

Shouldn't it be an AND-expression then?

Like this:

    ICriteria criteria = session.CreateCriteria(typeof (Person));
    criteria.CreateAlias("Names", "name");
    foreach (string name in namesToFind)
    {
        criteria.Add(Expression.Like("name.Value", "%" + name + "%"));
    }

EDIT

Ok. To match the query you gave above, with a slight change to avoid the join:

    ICriteria criteria = s.CreateCriteria(typeof(Person));
    foreach (string name in namesToFind)
    {
        criteria.Add(Subqueries.PropertyIn("Id",
            DetachedCriteria.For<Name>()
                .Add(Restrictions.Like("Value", name, MatchMode.Anywhere))
                .SetProjection(Projections.Property("Person"))));
    }

This requires that you have a mapped property named Person on the Name class.

Answer 2

There are various ways this can be done. You could use an exists subquery for each name. Or, you could join on names, add a name like '%blah%' for each name, group by person and add a having count(*) = nameCnt. However, the Having clause is not supported in ICriteria so you'd need to use HQL.

Answer 3

This is the sort of sql I'm wanting to get:

select * from person where Id in
(
select person.id from person inner join [name]
on person.id = name.personId
 where name.value like '%jane%'
)
and id in
(
select person.id from person inner join [name]
on person.id = name.personId
 where name.value like '%janice%'
) 
and id in
(
select person.id from person inner join [name]
on person.id = name.personId
 where name.value like '%louise%'
)