Parameterizing an HQL IN Clause Using HqlBasedQuery?

Question

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

eg.

// 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 it won’t work, as far as I wish! I'm 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); 

It's just ugly, and I tried to make it not as ugly and as short as I can. If anyone has a good way to accomplish this, please let me know.

I also see that Jeff asked similar questions about how to do this in SQL: 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 make the names 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();