hql - NHibernate and custom SQL subquery (using temp table) -

is possible use custom sql sub query "in" parameter. currently, build sub query (subqueryestate) it's complicated chunk of sql can take time process larger datasets. code generating query using our existing sub query follows:

session.queryover(() => calias) .withsubquery.whereproperty(x => x.cpe.id).in(subqueryestate) .joinalias(x => x.costs, () => acosts, jointype.leftouterjoin) .joinalias(x => x.open, () => aopen, jointype.innerjoin) .list(); 

to improve speed of execution have idea use temp table (transactional life-cycle) populate id's. idea either join temp table or use simpler sub query (select id temp_table) instead of more complicated original.

can use table isn't mapped sub query in nhibernate? can write custom sql or create detached criteria pass parameter in clause? preserve fact nhibernate producing correct sql remainder of query.

ideally like:

session.queryover(() => calias) .withsubquery.whereproperty(x => x.cpe.id).in("select id temp_table") .joinalias(x => x.costs, () => acosts, jointype.leftouterjoin) .joinalias(x => x.open, () => aopen, jointype.innerjoin) .list(); 

thoughts? ideas? there might more elegant solution haven't thought about.

in end used temporary tables solve problem. our app uses firebird database created 4 global temporary tables having "transaction" life-cylce. is, data in temp table valid life of transaction.

the sql used create temp table (note created 4 satisfy our use case)

create global temporary table temp_table_id1 (id bigint not null) on commit delete rows; create index idx_temp_table_id1 on temp_table_id1(id);

using temp tables can systematically populate them id's master query. filter subsequent queries either using sub-query or join temp table faster approach using large sub-query each "downstream" query. performance dramatically improved using temp tables , because tables valid life of transaction means don't have worry cross-transaction pollution and/or purging tables of data.

a tidy solution.

global temporary tables (firebird) http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html