NHibernate IN Expression / Restrictions Hitting 2100 Parameter Limit SQL Server

Question

Is there a way to force NHibernate to run a query without executing it as a parameterized query. Basically I'm running into an issue where I am hitting SQL Server's 2100 parameter limit.

I'm hitting a limit because of an "IN" restriction on my query. For reasons I won't get into details about I need to use an NHibernate In Restriction on my query.

Query.Add(Restrictions.In("df.ID", myList));

I've run NHibernate profiler on the query and NHibernate is passing every "In" value as a parameter rather than a literal value.

myList is an array with over 5201 values. I've researched online, there is no limit on how many IN values you can pass to SQL so if I can get NHibernate to pass the values as literal values instead of parameters that should fix my problem.

Any help would be appreciated. Also please don't comment on my use of the IN statement, I've run into an issue where my query requires me to use the IN statement in this way and I can't approach it any other way.

You could use a [table-valued parameter](https://stackoverflow.com/questions/3701364/is-it-possible-to-use-sqldbtype-structured-to-pass-table-valued-parameters-in) instead. — Dan Guzman, Jun 05 '19 at 02:18

Roman Artiukhin · Answer 1 · 2019-10-20T10:57:41.287

If you are OK with literal values you can use the following class:

    /// <summary>
    /// IN expression with inlined parameters like  "Id IN (1, 2, 3)"   
    /// </summary>
    public class InlineInExpression : SimpleExpression
    {
        //Note!!! this works properly only for numeric types. String list requires escaping and wrapping each value in `[escapedValue]`
        public static InlineInExpression For<T>(string propertyPath, IEnumerable<T> list)
        {
            return new InlineInExpression(propertyPath, string.Join(", ", list));
        }

        /// <summary>
        /// IN expression ctor
        /// </summary>
        /// <param name="propertyPath">Property path</param>
        /// <param name="inExpression">Coma-delimited parameters like "1, 2, 3"</param>
        private InlineInExpression(string propertyPath, string inExpression)
            :base(propertyPath, null, inExpression)
        {
        }

        public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
        {
            SqlString[] columnNames =
                CriterionUtil.GetColumnNamesForSimpleExpression(PropertyName, null, criteriaQuery, criteria, this, Value);

            if (columnNames.Length != 1)
                throw new HibernateException("This expression supports only single column properties.");

            return new SqlString(columnNames[0], " IN (", Op, ")");
        }
    }

And example of usage:

Query.Add(InlineInExpression.For("df.ID", myList));

Please note it's working properly only for numeric values (int, long, etc). If string handling is required - you should implement it yourself.

You can also adapt this approach to your solution to avoid using SQLCriterion with table aliases and column names.

score 1 · Accepted Answer · answered Jun 15 '19 at 01:55

I was able to solve this by using a SQL Criterion statement added to my query and in conjunction using a table-valued parameter.

Rather than this:

Query.Add(Restrictions.In("df.ID", myList));

I used this:

Query.Add(new SQLCriterion(new SqlString(string.Format("this_.ID NOT IN (SELECT * FROM [dbo].[Explode] ('{0}'))", siteProdIds)), new object[0], new IType[0]))

I then created this function on my database:

CREATE FUNCTION [dbo].[Explode](
    @string    varchar(MAX) -- '1,2,3,5,6,7'
)
RETURNS @table TABLE(element int)
AS
BEGIN
DECLARE @temp varchar(MAX), @delimPos AS tinyint = 0         
SET @temp= LTRIM(RTRIM(@string))
WHILE CHARINDEX(',',@temp) > 0 
BEGIN 
SET @delimPos = CHARINDEX(',',@temp)
INSERT INTO @table(element) VALUES (CAST((LEFT(@temp,@delimPos-1)) AS int))
SET @temp= RTRIM(LTRIM(SUBSTRING(@temp,@delimPos+1,LEN(@temp)-@delimPos))) 
END 
INSERT INTO @table(element) VALUES (CAST((@temp) AS int))
RETURN
END

Might be better to use "{alias}" instead of "this_" to avoid possible problems in the future. — brz, Jul 28 '22 at 05:35

score 1 · Answer 3 · answered Jul 27 '22 at 10:00

I wanted to filter on a list of ID's (int32) and this worked brilliant for me:

var ids = new List<int> { 1, 2, 3 };
var idsToIncludeAsCommaSeparatedString = string.Join(", ", ids);
var criterion = Expression.Sql(new SqlString($"{{alias}}.XXX IN ({idsToIncludeAsCommaSeparatedString})"));
query = query.Where(criterion);

The {alias} placeholder will be replaced by the alias of the queried entity.

XXX is the name of the column.

There is no risk of SQL injection since it's just a list of integers that are being joined in a comma-separated string.

NHibernate IN Expression / Restrictions Hitting 2100 Parameter Limit SQL Server

3 Answers3