NHibernate - How to work around the parameter count limitation in SQL Server

Question

I have a website that displays data form SQL Server in the form of a simple table with filters, sorting, page navigation, etc. I'm using Fluent NHibernate as ORM and the query code looks as follows:

public IList<Operation> GetResults(UserCommand command)
{
    var result = Session.Query<Operation>();

    if (command.Ids != null)
        result = result.WhereRestrictionOn(o => o.Id).IsIn(command.Ids);

    // ... other filters ...

    return result.Skip(command.Page * command.PageSize).Take(command.PageSize).List();        
}

The problem is that command.Ids (and some other input parameters) may contain a lot of values. And if the number is more than 2100 the query execution fails with the following error

System.Data.SqlClient.SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Splitting the query into smaller chunks that have less than 2100 parameters is not an option because with multiple queries I would have no way of knowing how many records to Skip and Take to display the required page.

Is there any other workaround to reduce the number of parameters?

Answer 1

Well, after having a look at NHibernate source code I was able to find a solution that works and is not particularly ugly. The key thought is to generate SQL 'IN' expression without using parameters.

The first thing to do is make an ICriterion class for this expression. This should be done carefully to avoid possible SQL injections.

public class ParameterlessInExpression : AbstractCriterion
{
    private readonly IProjection _projection;
    private readonly object[] _values;

    /// <summary>
    /// Builds SQL 'IN' expression without using parameters
    /// NB: values must be an array of integers to avoid SQL-Injection.
    /// </summary>
    public ParameterlessInExpression(IProjection projection, int[] values)
    {
        _projection = projection;
        _values = values.Select(v => (object)v).ToArray();
    }

    public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        if (_values.Length == 0)
            return new SqlString("1=0");

        var result = new SqlStringBuilder();
        var columnNames = CriterionUtil.GetColumnNames(null, _projection, criteriaQuery, criteria, enabledFilters);    

        for (int columnIndex = 0; columnIndex < columnNames.Length; columnIndex++)
        {
            SqlString columnName = columnNames[columnIndex];

            if (columnIndex > 0)
                result.Add(" and ");

            result.Add(columnName).Add(" in (").Add(StringHelper.ToString(_values)).Add(")");
        }

        return result.ToSqlString();
    }

    // ...
    // some non-essential overrides omitted here
    // ...
}

Next we make a nice IQueryOver extension

public static IQueryOver<TRoot, TSubType> WhereIn<TRoot, TSubType>(this IQueryOver<TRoot, TSubType> query, Expression<Func<TSubType, object>> expression, int[] values)
{
    query.UnderlyingCriteria.Add(new ParameterlessInExpression(Projections.Property<TSubType>(expression), values));
    return query;
}

And finaly use this in a query:

if (command.Ids != null)
    result = result.WhereIn(o => o.Id, command.Ids);