ServiceStack ORMLite 5.11.0 SQL Issues - Too Many Parameters

user_16770278 0
Viewed 123

We are seeing an issue where with the ServiceStack ORMLite 5.11.0 version we are getting the below error related to reaching teh maximum of 2100 parameters.

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

We recently upgraded from ServiceStack ORMLite 4.0.46 version where we are using the same dataset and not seeing this issue, so seems like a change in logic on how ORMLite is handling the dataset now.

Is there anyone that has run into this similar issue and what was done to resolve the issue to get it working with the newer version of ServiceStack ORMLite?

SQL Generated from 4.0.46 version:

SELECT "POOL_VEH_UID", "POOL_UID", "UNIT_ID", "START_DT", "END_DT", "CRE_BY_ID", "CRE_TS", "UPD_BY_ID", "UPD_TS"
FROM "POOL_VEH"
WHERE (("UNIT_ID" In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,...2110) AND ("POOL_UID" = CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))) AND ("END_DT" is null));

SQL Generated from 5.11.0 version:

SELECT "POOL_VEH_UID", "POOL_UID", "UNIT_ID", "START_DT", "END_DT", "CRE_BY_ID", "CRE_TS", "UPD_BY_ID", "UPD_TS"
FROM "POOL_VEH"
WHERE (("UNIT_ID" In (@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,...@2110) AND ("POOL_UID" = CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))) AND ("END_DT" is null));
ormlite-servicestack
1 Answers

The issue is because OrmLite has switched to using parameterized values which your large IN() statement exceeds the maximum limit that SQL Server allows.

You can avoiding large parameterized values and revert to using a parameterless IN statement with a custom SQL fragment:

.Where($"UNIT_ID IN ({ids.SqlJoin()})");

If preferred you can also use typed Column and Table names.

user_857850
Related