NHibernate-如何解决在SQL Server中参数计数限制的问题

Question

NHibernate-如何解决在SQL Server中参数计数限制的问题

5

我有一个网站，以简单的表格形式显示来自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个参数并不是一个选项，因为使用多个查询，我将无法知道要显示所需页面需要跳过和获取的记录数。

是否有其他方法来减少参数数量？

- holdenmcgrohen

据我所知没有。当我们遇到类似的问题时，我们会将ID保存在一个表中并进行连接。我不知道这是否适用于您的情况，因为我不知道ID是如何加载的。 - Erik Sundström

从HTTP请求参数映射ID数组。我猜将它们保存在临时表中并进行连接可能是一个选项，但有些东西告诉我，这样的操作所需的C#代码会很丑陋。 - holdenmcgrohen

另一个不太好的解决方案是使用原始SQL，而不使用参数。 - Erik Sundström

不确定是否可以通过使用子查询来生成ID列表，但如果可以的话，这可能是一个解决方法。 - Cole W

当然，如果有一种使用子查询生成它的方法，那将是最好的方法。但是 command.Ids 是用户输入的，完全不可预测。 - holdenmcgrohen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- holdenmcgrohen · Accepted Answer

浏览了NHibernate源代码后，我找到了一个能够工作且不算丑陋的解决方案。关键是生成SQL“IN”表达式而不使用参数。

首先要做的是为此表达式创建一个ICriterion类。这应该小心谨慎地完成，以避免可能的SQL注入。

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