Gareth Farrington Gareth Farrington - 3 months ago 22
C# Question

Parameterizing a HQL IN clause using HqlBasedQuery?

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

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