Using SetParameterList and the LIKE keyword

Question

I'm using NHibernate and I have a query which I want to run, which involves returning the whole table plus a count and group by. From what I've read online you can't do this with NHibernate Criteria.

To get round this, I'm using a named query:

SELECT id, COUNT(id) AS myCount

FROM foo INNER JOIN bah

ON foo.id = bah.fooId

WHERE foo.Name LIKE :name

GROUP BY fooId

this works fine when I only want to pass in one parameter.

What I actually want to achieve though is:

SELECT id, COUNT(id) AS myCount FROM

foo INNER JOIN bah ON foo.id = bah.fooId

WHERE foo.Name LIKE :name1

OR foo.Name LIKE :name2

OR foo.Name LIKE :name3

GROUP BY fooId

the number of parameters is changeable - so there could be 2 or 3 or 4 names that I want to match.

I got round it using a criteria with the following code:

ICriteria criteria = session.CreateCriteria(typeof (Foo)).CreateCriteria("Bah");

Disjunction disjunction = Restrictions.Disjunction();

foreach (Foo foo in fooToFind)

{

disjunction.Add(Restrictions.Like("Name", "%" + Foo.Name + "%"));

}

criteria.Add(disjunction);

result = criteria.List();

(like I said above though it doesn't look like I can use this because I can't return a count as part of the return)

So I tried using query.SetParameterList:

IQuery query = session.GetNamedQuery("myNamedQuery");

query.SetParameterList("name", new string[] {"eeeny", "meeny"});

however this seems to gives me the sql:

SELECT id, COUNT(id) AS myCount

FROM foo INNER JOIN bah

ON foo.id = bah.fooId

WHERE foo.Name LIKE 'eeny','meeny'

GROUP BY food

so nHibernate doesn't like the comma.

Changing the query to

SELECT id, COUNT(id) AS myCount

FROM foo INNER JOIN bah

ON foo.id = bah.fooId

WHERE foo.Name IN (:name)

GROUP BY fooId

works well, but doesn't give me the results I want.

Does anyone know how I can achieve this result - using any nhibernate method?

SELECT id, COUNT(id) AS myCount FROM

foo INNER JOIN bah ON foo.id = bah.fooId

WHERE foo.Name LIKE :name1

OR foo.Name LIKE :name2

OR foo.Name LIKE :name3

GROUP BY fooId

Thanks!

Answer 1

It seems that a named query is not a right choice for your scenario.Instead you should build your query on the fly something like this:

    var querybase="select f.Id,Count(f.Id) from foo WHERE {0} group by f.Id";
    var whereClause=new StringBuilder();
    whereClause.Append("0=1 ");
    foreach(var name in names)
    {
       whereClause.Append(string.Format("OR f.Name Like '{0}' ",name));
    }

    var query=string.Format(querybase,whereClasue);

For the sake of simplicity I ignore the parameters and add name value to where clause which is not recommended due to opening a place for SQL injection but you can add parameters names here and then set your parameters values in your query.

Answer 2

Your original query

SELECT id, COUNT(id) AS myCount FROM
foo INNER JOIN bah ON foo.id = bah.fooId 
WHERE foo.Name LIKE :name1
OR foo.Name LIKE :name2 
OR foo.Name LIKE :name3
GROUP BY fooId

For nHibernate

SELECT id, COUNT(id) AS myCount FROM
foo INNER JOIN bah ON foo.id = bah.fooId 
WHERE 
foo.Name IN (:names)
GROUP BY fooId

In theory, yours should work if

1) you set yours up as a Named Query
2) you have an entity setup as the <return class="">
3) you use SetParameterList
4) names is a string array or ArrayList

e.g your resultant hbm file

  <sql-query name="MyFooCount" cacheable="false" read-only="true">
  <return alias="" class="FooCountEntity" />
    <![CDATA[
Your query here...
    ]]>
  </sql-query>

e.g your C# file

string [] mynames = {"peter", "olivia", "charles"};
query.SetParameterList ("names", mynames);