Parameterizing a HQL IN clause using HqlBasedQuery?

Question

How do you pass a list of things for the 'in' clause in Nhibernate HQL?

e.g.

// data input from the user interface, not known at compile time
object[] productIds = {1, 17, 36, ... }; 
string hqlQuery = @"
                from Product as prod
                where prod.Id in ( ? )";
HqlBasedQuery query = new HqlBasedQuery(typeof(Product), hqlQuery, productIds)
ActiveRecordMediator.ExecuteQuery(query);

Now, this isn't going to work, as much as I wish it would! Am I really stuck doing something like this:

// data input from the user interface, not known at compile time
object[] productIds = {1, 17, 36, ... }; 
string hqlQuery = @"
                from Product as prod
                where prod.Id in ( {0} )";

// build string array of the right number of '?' characters
string[] paramStringArray = new String('?', productIds.Length).ToCharArray().Select(item => item.ToString()).ToArray();
// join to make '?, ?, ?, ?, ?'
string parameterString = string.Join(", ", paramStringArray);
hqlQuery = string.Format(hqlQuery , parameterString);

HqlBasedQuery query = new HqlBasedQuery(typeof(Product), hqlQuery, productIds)
ActiveRecordMediator.ExecuteQuery(query);

That's just ugly and I've tried to make it as not ugly and short as I can. If anyone has a nice way of accomplishing this please let me know.

Also I see Jeff asked a similar questions about how to do this on SQL: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause This is basically the same question I just want to know how to do it from HQL. That's why I'm making the titles so similar.

Answer 1

Use SetParameterList(). See:

• http://www.nhforge.org/doc/nh/en/index.html#manipulatingdata-queryinterface
• http://www.billsternberger.net/nhibernate/nhibernate-icriterion-query-wc/