Parameterizing a HQL IN clause using HqlBasedQuery?

c#, castle-activerecord, hql, nhibernate

Solution

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();

Problem

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.

Original source