NHibernate: Subqueries. Does not work

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?

+3
source share
2 answers

Try creating criteria or an alias on the Foo path in the Bar class in your subquery, and then apply the eaual constraint.

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

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

, .

, ( ) Mapped Reference .

, "childEntity.FK = parentEntity.Key" , Expression.EqProperty(..,..), Expression.Eq(..,..)

, :

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

:

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

Source: https://habr.com/ru/post/1735531/


All Articles