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: Parameterize an 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().

Code snippet from billsternberger.net:

ArrayList stateslist = new ArrayList();
stateslist.Add("TX");
stateslist.Add("VA");

string hql = String.Format("FROM Contact c where State in (:states)");
SimpleQuery<Contact> q = new SimpleQuery<Contact>(hql);
q.SetParameterList("states", stateslist);

Contact[] result = q.Execute();