NHibernate: CreateCriteria and Exists clause

nhibernate exists createcriteria
cbp picture cbp · Nov 18, 2009 · Viewed 13.9k times · Source

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)

Answer

tolism7 picture tolism7 · Nov 18, 2009

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 questions

What are the First and Second Level caches in Hibernate?

Can anyone explain in simple words what First and Second Level caching in Hibernate are?

Read More
NHibernate.MappingException: No persister for: XYZ

Now, before you say it: I did Google and my hbm.xml file is an Embedded Resource. Here is the code I am calling: ISession session = GetCurrentSession(); var returnObject = session.Get<T>(Id); Here is my mapping file …

Read More
Best way to convert IList or IEnumerable to Array

I have a HQL query that can generate either an IList of results, or an IEnumerable of results. However, I want it to return an array of the Entity that I'm selecting, what would be the best way of accomplishing …

Read More