How to avoid the "Too many parameters..." error when querying large amounts of persistent objects by their keys

Scenario

The ability to execute specific criteria operator greatly depends on the underlying database system. There are known limitations for database providers with regard to the maximum number of query parameters (e.g., see Maximum Capacity Specifications for Microsoft SQL Server).

You may hit this limitation when using the InOperator to query a list of persistent objects by their keys. In this case, you may receive the "Too many parameters were provided in this RPC request. The maximum is 2100", "The incoming request has too many parameters. The server supports a maximum of 2100 parameters", "ORA-01795 maximum number of expressions in a list is 1000"  or similar errors.

Solution

Option 1

For this particular task in XPO, we recommend using the Session.GetObjectsByKey  method. This specialized method would split queries into batches when retrieving data from the database. Also, if some of the required objects are already loaded, it will not query them from the database.

Option 2

Alternatively, you can manually handle this situation in your application code to prevent using an unsupported number of parameters in queries.