I am trying to get sql as follows using NHibernate api criteria:
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 that have many Bazes. I want to get all the Foos that have bars with Bazes.
For this, the preferred deferred criterion is as follows:
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 an exception: System.ArgumentException: Could not find the provider of information about the matching criteria: bar.FooId = foo.Id and bar.Bazes is not empty.
Is this a bug with NHibernate? Is there a better way to do this?
source
share