NHibernate: CreateCriteria and Exists clause

createcriteriaexistsnhibernate

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)

Best Answer

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

Related Solutions

R – CreateCriteria and MONTH

ICriteria supports arbitrary SQL as a restriction. Therefore you could do:

var criteria = session.CreateCriteria(typeof(Obs))
    .Add(Expression.Sql("MONTH({alias}.Datum) = ?", 11, NHibernateUtil.Int32);
var results = criteria.List<Obs>();

This will execute a SQL query with {alias} replaced by the alias that NHibernate is using for the Obs table. Of course, this limits your portablility to other databases, as SQL is now embedded in your query.

Another thing to remember is that the names you're using here are the mapped property and class names, not the underlying column and table names.

Mysql – Best way to test if a row exists in a MySQL table

You could also try EXISTS:

SELECT EXISTS(SELECT * FROM table1 WHERE ...)

and per the documentation, you can SELECT anything.

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

Best Answer

Related Solutions

R – CreateCriteria and MONTH

Mysql – Best way to test if a row exists in a MySQL table

Related Topic