How to construct NHibernate criteria to find parents who has all specified children

Question

A Project can have many Parts. A property on Part is Ipn, which is a string of digits.

• Project "A" has Parts "1", "2", "3"
• Project "B" has Parts "2", "3", "4"
• Project "C" has Parts "2"
• Project "D" has Parts "3"

I want to find all Projects that have all of the specified parts associated with it. My current query is

            var ipns = new List<String> { "2", "3" }

            var criteriaForIpns = DetachedCriteria
                .For<Part>()
                .SetProjection(Projections.Id())
                .Add(Expression.In("Ipn", ipns));

            _criteriaForProject
                .CreateCriteria("Ipns")
                .Add(Subqueries.PropertyIn("Id", criteriaForIpns));

This gives me back all Projects that have any of the parts, thus the result set is Projects A, B, C, and D.

The SQL where clause generated, looks something like

WHERE    part1_.Id in (SELECT this_0_.Id as y0_
                   FROM   Parts this_0_
                   WHERE  this_0_.Ipn in ('2' /* @p0 */,'3' /* @p1 */))

My desired result would only be Projects A and B. How can I construct the NHibernate criteria to get the result set that I need?

The number of parts I search on can vary, it can be n number of parts.

Answer 1

Your query requires that we make two joins from Project to Part. This is not possible in Criteria.

HQL
You can express this query directly in HQL.

var list = session.CreateQuery( @"
    select  proj from Project proj
        inner join proj.Parts p1
        inner join proj.Parts p2
    where   p1.Id=:id1
    and p2.Id=:id2
    " )
    .SetInt32( "id1", 2 )
    .SetInt32( "id2", 3 )
    .List<Master>();

Criteria
With the Criteria API, you would query for those Projects that have one of the specified Parts, and the filter the results in C#.

Either have the criteria eager load Project.Parts, or map that as lazy="extra".

Then, using your existing criteria query from above.

// Load() these if necessary
List<Parts> required_parts;

var list = _criteriaForProject.List<Project>()
    .Where( proj => {
        foreach( var p in required_parts ) {
            if (!proj.Parts.Contains( p ))) {
                return false;
            }
            return true;
        }
    });

// if _criteriaForProject is a Detached Criteria, that would be:
var list = _criteriaForProject.GetExecutableCriteria( session )
    .List<Project>()
    .Where( // etc