Nhibernate Expression.In limit

Question

I am querying the Nhibernate criteria query with more then 2100 values for In clause. I do something like Session.CreateCriteria(typeof()).Add(Expression.In("fieldName",arrayValue)) Where arrayValue contains more then 2100 values. I face error Exception occurred: UnknownError NHibernate.ADOException: could not execute query ..then the query with more then 3000 values in array. with some google help we found out that IN clause in Sql supports only till 2100 values. Does anyone has faced similar issue earlier? We do not want to change the query as it is written in some generic way and not customized one.

Answer 1

This is a limitation of SQL Server. I wouldn't suggest doing this, but if you insist, you could work around it by creating a table-value sql function (see http://www.dzone.com/snippets/function-getting-comma) that splits up a string by commas (or whatever delimiter you want) and returns the values as a table, and then pass in all your ID's as (say) a comma separated list in 1 parameter and use a SQLCriterion in your criteria query.

eg:

  criteria.Add(
      new SQLCriterion("{alias}.ID IN (SELECT element FROM dbo.GetCSVValues(?))", 
          new[]{csvListOfIds}, 
          new[]{NHibernateUtil.String}))

Answer 2

You could split the array into multiple batches, query multiple times, and then combine the result.