SqlException when querying content items with more than 5000 item

[tonydev24]

Describe the bug

When querying content items to display them in a page, if the number of items is high an SqlExecption is thrown.

To Reproduce

Steps to reproduce the behavior:

1. Create a content item and fill (import) 5000 items

2. Query the items this way:
   @{ var query = await QueryManager.GetQueryAsync("MyQuery"); var contentItems = await QueryManager.ExecuteQueryAsync(query, new Dictionary<string, object>()); var datasource = contentItems.Items; }

3. The following exception is thrown
   An unhandled exception occurred while processing the request.
   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.
   System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
   System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
   System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
   System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot, Thread threadPoolThread)
   Dapper.SqlMapper.QueryAsync(IDbConnection cnn, Type effectiveType, CommandDefinition command) in SqlMapper.Async.cs
   YesSql.Store.ProduceAsync<T, TState>(WorkerQueryKey key, Func<TState, Task> work, TState state)
   YesSql.Session.GetAsync(long[] ids, string collection)
   YesSql.Session.GetAsync(long[] ids, string collection)
   OrchardCore.Queries.Sql.SqlQuerySource.ExecuteQueryAsync(Query query, IDictionary<string, object> parameters)
   OrchardCore.Queries.Sql.SqlQuerySource.ExecuteQueryAsync(Query query, IDictionary<string, object> parameters)
   AspNetCoreGeneratedDocument.Views_Widget_InstrumentDataGrid.ExecuteAsync() in Widget-InstrumentDataGrid.cshtml
   var contentItems = await QueryManager.ExecuteQueryAsync(query, parameters);

[lampersky]

this magic number 2100 reminded me about the issue from the past
#8410

[MikeAlhayek]

The server supports a maximum of 2100

This is a SQL Server limitation set by configs. Try enabling the Mini-profiler feature and checkout the raw query to see why it has so many parameters.

you can also enable debuging of yessql in your project by adding this to nlog settings. <logger name="YesSql" minlevel="Debug" writeTo="file" />

You'll get all the raw queries printed into your log file for evaluation.

[douwinga]

I think this is an issue with the Return Documents option of the SQL queries

If your query finds more than 2100 content items you will hit this error.

It will just pass all the content item ids it finds to _session.GetAsync which will add each content item id as a parameter

[douwinga]

The ContentManager likely has the same issue with GetAsync when you pass on more than 2100 content item IDs.

[tonydev24]

Thank you, the issue is resolved after disabling the Return Documents option.

[douwinga]

Without it checked though, you won't get the full content items. So unchecking that doesn't fix the bug

[tonydev24]

Ok i'll keep it open for the team to check

[sebastienros]

Use paging