c# - NHibernate - 如何解决 SQL Server 中的参数计数限制

Question

我有一个网站，它以带有过滤器、排序、页面导航等功能的简单表格的形式显示来自 SQL Server 的数据。我使用 Fluent NHibernate 作为 ORM，查询代码如下所示:

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

问题是 command.Ids (以及其他一些输入参数)可能包含很多值。如果数量超过 2100，则查询执行失败并出现以下错误

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.

将查询拆分为参数少于 2100 个的较小块是不可行的，因为对于多个查询，我无法知道要 Skip 和 Take 的记录数> 显示所需的页面。

还有其他解决方法可以减少参数数量吗？

Answer 1

嗯，在查看了 NHibernate 源代码之后，我找到了一个可行且不是特别难看的解决方案。关键思想是在不使用参数的情况下生成 SQL 'IN' 表达式。

要做的第一件事是为此表达式创建一个 ICriterion 类。应小心执行此操作以避免可能的 SQL 注入(inject)。

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
    // ...
}

接下来我们制作一个不错的 IQueryOver 扩展

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;
}

最后在查询中使用它:

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