This isn't all written and working so here's the basic idea. We have a simple query like this:
IPredicateExpression filter = new PredicateExpression();
filter.Add(TaskFields.DueDate < DateTime.Now);
// The portion of the query that restricts the results to the tasks that a user has permission to view.
filter.Add(...);
TaskCollection tasks = new TaskCollection();
tasks.GetMulti(filter);
But the permission portion needs to flexible and we want the the SQL to be drawn from configuration files. So then we get something like this:
// Use ADO .NET to get the ids of the tasks that the user has permission to view.
IList<int> taskIds = ...;
IPredicateExpression filter = new PredicateExpression();
filter.Add(TaskFields.DueDate < DateTime.Now);
filter.Add(new FieldCompareRangePredicate(TaskFields.TaskID, taskIds.ToArray()));
TaskCollection tasks = new TaskCollection();
tasks.GetMulti(filter);
But we will run into the SQL Server limit on the number of parameters:
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.
We're looking for some way to use the results of an arbitrary SQL query in a FieldCompareSetPredicate (or anything similar that will address our problems.)
We're using version 2.6.