NHibernate: Subqueries.Exists not working
- by cbp
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?
