NHibernate: CreateCriteria and Exists clause

Question

How can I write the following SQL using CreateCriteria:

SELECT * FROM FooBar fb
WHERE EXISTS (SELECT FooBarId FROM Baz b WHERE b.FooBarId = fb.Id)

Barnacle

Answer 1

I worked out how to do this using the IsNotEmpty expression. Here it is using NHibernate Lambda Extensions:

Session.CreateCriteria<FooBar>()
    .Add(SqlExpression.IsNotEmpty<FooBar>(x => x.Bazes))
    .List<FooBar>();

Barnacle

Indeed... and you gave me the idea that it can be done in a simpler way and without the Nhibernate Lambda extensions. I have edited my answer to include that option. – Gripe

Answer 2

Here is how you can do it:

var fooBars = Session.CreateCriteria<FooBar>()
        .Add(Restrictions.IsNotEmpty("Bazs")).List<FooBar>();

...assuming there is a collection property (one-to-many) "Bazs" in the FooBar object.

Alternatively you could use detached criteria like that:

DetachedCriteria dCriteria = DetachedCriteria.For<Baz>("baz")
        .SetProjection(Projections.Property("baz.FooBarId"))
        .Add(Restrictions.EqProperty("baz.FooBarId", "fooBar.Id"));

var fooBars = Session.CreateCriteria<FooBar>("fooBar")
        .Add(Subqueries.Exists(dCriteria)).List<FooBar>();

Gripe

This helped me resolve the NHIBERNATE ERROR : "Could not find a matching criteria info provider to", thank you for the simple example. – Mackenzie

Answer 3

Having just solved a related problem and eventually arrived at a solution I thought I'd share the answer here:

Assuming you want the original questions query, with an additional condition on the sub-query:

SELECT * FROM FooBar fb
WHERE EXISTS (SELECT FooBarId FROM Baz b WHERE b.FooBarId = fb.Id
              AND Quantity = 5)

Assuming you have a reference on the Baz class to the parent, called, say FooBarRef [ in Fluent Map class you'd use the References() method ], you would create the query as follows:

DetachedCriteria dCriteria = DetachedCriteria.For<Baz>("baz")
        .SetProjection(Projections.Property("baz.FooBarId"))
        .Add(Expression.EqProperty("this.FooBarId", "FooBarRef.Id"))
        .Add(Expression.Eq("baz.Quantity", 5));

var fooBars = Session.CreateCriteria<FooBar>("fooBar")
        .Add(Subqueries.Exists(dCriteria)).List<FooBar>();

I'm not 100% convinced about hard coding of the alias "this", which is the alias NHibernate automatically assigns to the root entity (table) in the query, but it's the only way I've found to reference the key of the parent query's table from within the sub-query.

Yakka