NHibernate IN Expression / Restrictions Hitting 2100 Parameter Limit SQL Server

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.

Answer

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