NHibernate: Subqueries.Exists not working

Question

I am trying to get sql like the following using NHibernate's criteria api:

SELECT * FROM Foo
   WHERE EXISTS (SELECT 1 FROM Bar 
                 WHERE Bar.FooId = Foo.Id
                 AND EXISTS (SELECT 1 FROM Baz
                            WHERE Baz.BarId = Bar.Id)

So basically, Foos have many Bars and Bars have many Bazes. I want to get all Foos that have Bars with Bazes.

To do this, a detached criteria seems best, like this:

var subquery = DetachedCriteria.For<Bar>("bar")
    .SetProjection(Projections.Property("bar.Id"))
    .Add(Restrictions.Eq("bar.FooId","foo.Id")) // I have also tried replacing "bar.FooId" with "bar.Foo.Id"
    .Add(Restrictions.IsNotEmpty("bar.Bazes"));

return Session.CreateCriteria<Foo>("foo")
     .Add(Subqueries.Exists(subquery))
     .List<Foo>();

However this throws the exception: System.ArgumentException: Could not find a matching criteria info provider to: bar.FooId = foo.Id and bar.Bazes is not empty

Is this a bug with NHibernate? Is there a better way to do this?

Answer 1

Try to create criteria or alias on the path of Foo in the Bar class in your subquery and then apply the eaual restriction.

var subquery = DetachedCriteria.For<Bar>("bar")
    .SetProjection(Projections.Property("bar.Id"))
    .Add(Restrictions.IsNotEmpty("bar.Bazes"))
    .CreateCriteria("Foo")
         .Add(Restrictions.Eq("bar.FooId","Id"));

or CreateAlias("Foo","foo")

var subquery = DetachedCriteria.For<Bar>("bar")
    .SetProjection(Projections.Property("bar.Id"))
    .Add(Restrictions.IsNotEmpty("bar.Bazes"))
    .CreateAlias("Foo","foo")
    .Add(Restrictions.Eq("bar.FooId","foo.Id"));