We are attempting to create a query that is using a range of entity id's as a filter. The query is quite complicated, and the range of id's is coming from a separate query that cannot be written in LLBLGen.

Normally in this case we use a FieldCompareRangePredicate to create the WHERE..IN clause, but in this case the amount of results may be larger than the limit for parameters in the query.

We are wondering if there is a way to accomplish this. One thought was to "create" a derived table in code using the entity id values and use that for filtering, but we don't even know if this is possible let alone viable. What is the best way to do this?

mafuba wrote:

Normally in this case we use a FieldCompareRangePredicate to create the WHERE..IN clause, but in this case the amount of results may be larger than the limit for parameters in the query.

How many? maybe you could add multiples FieldCompareRangePredicate to your filter. The best way is write the filter in a subquery manner (FieldCompareSetPredicate). Could you post the approximate SQL of that (the separate query that cannot be written in LLBLGen)?

Also, What LLBLGen version are you using?

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.

Then... I only could recommend to you read these recommendations: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=13502