(Fluent)NHibernate SELECT IN

Question

Given the following tables:

tool
 *toolid
 *n other fields

process
 *processid
 *n other fields

toolprocess
 *toolprocessid
 *toolid
 *processid
 *n other fields

When trying to select all tools for a specific process I get up to a few thousand selects on toolprocess where my Linq looks like this:

from tool in tools
where toolprocesses.Any(t=>t.Tool.Id==tool.Id)
select tool

where toolprocesses contains the list of toolprocesses with the same processid

In SQL I would just write

SELECT * FROM TOOL WHERE toolid IN 
    (SELECT TOOLID FROM TOOLPROCESS WHERE processid = 'someid');

It takes almost no time and works as expected

How can I get NHibernate to create this query (or something similar)?

Answer 1

I don't know if you can do it in Query, but you can do it in QueryOver / Criteria.

In QueryOver it would look like:

var subQuery = QueryOver.Of<Toolprocess>()
                        .Where(x => x.Process.Id == id) 
                        .Select(x => x.Tool.Id);

var result = session.QueryOver<Tool>()
                    .WithSubquery.WhereProperty(x => x.Id).In(subQuery)
                    .List();

http://www.philliphaydon.com/2010/09/28/queryover-with-nhibernate-3-lovin-it/

Alternatively, if you want to do Exists rather than In, I've blogged about it here:

http://www.philliphaydon.com/2011/01/19/revisiting-exists-in-nhibernate-3-0-and-queryover/

Answer 2

Try

from t in Session.Query<Tool>()
join tp in Session.Query<Toolprocess>() on t equals tp.Tool
where tp.Process.Id == 'someid'
select t;

I assume that you're using NH 3.X. This should be even faster than the Select...Where...In query.