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

Answer 2

I've just solved the same problem I was experiencing.

The solution for me was to qualify the 'child' entity's foreign key (pointing at the parent entity) with the 'Mapped Reference property' to that parent.

Also, since your 'childEntity.FK = parentEntity.Key" is actually equating a property with a property you want to use Expression.EqProperty(..,..) rather than Expression.Eq(.., ..)

So, I suggest changing this line:

Add(Restrictions.Eq("bar.FooId","foo.Id")) 

To this:

.Add(Restrictions.EqProperty("bar.Foo.Id","foo.Id"))