ExistsSubqueryExpression... How the heck do you use it???

Chuckles · **Posted:** Wed Apr 16, 2008 2:56 pm

I think I need to solve this problem with ExistsSubqueryExpression, but since I can't find any information about this, I could use some help.

Say for instance I have some beer coolers, and in each beer cooler I have several different types of beer. I am looking for the coolers that have PBR and Milwaukee's Best in it.

Here is the SQL I need to nhibernate to generate for me...

Code:

select bc.* from BeerCooler bc 
where exists
(select 1 from BeerCoolerContents bcc
where bcc.BeerName = 'PBR'
and bcc.Cooler_ID = bc.id)
and exists
(select 1 from BeerCoolerContents bcc
where bcc.BeerName = 'Milwaukee''s Best'
and bcc.Cooler_ID = bc.id)

I know my tables aren't normalized, and there are easier ways to do this query. I just want to see the ExistsSubqueryExpression in action in a Criteria query.

Any help would be greatly appreciated

wolli · **Posted:** Thu Apr 17, 2008 3:34 am

Something like this

Code:

DetachedCriteria contents = DetachedCriteria.For(typeof(BeerCoolerContents ),"bcc")
            .Add(Expression.Eq("BeerName", "Milwaukee")
            .Add(Expression.EqProperty("bcc.Cooler_ID", "bc.id"));

Criteria criteria = session.CreateCriteria(typeof(BeerCooler), "bc")
     .Add(Subqueries.Exists(contents.SetProjection(Projections.Property("bcc.id")));

You need another detached criteria for the second subquery. Maybe there's a possibility to do it with one subquery and pass the beer name to the detached criteria, but I'm not sure about this.

I'm not sure if the projection in the exists expression is really needed ( I used it in my case, but can't remember why), maybe it works without it, too.