after looking deeper into this issue we must say it is not easy to be fixed. The absolutely right fix would require quite some changes in order to produce (x.field IN (list1) OR x.field IN (list2) OR x.field IN (list3)), and needs to do the right thing in cases of NOT IN clauses too.
However, considering the fact that passing a large number of parameters is also something that needs to be avoided for some databases (and is probably not efficient neither) we thought of using a ";1;2;3;" representation of the values and split them on the server in order to avoid many parameters, but this has it's limitations as well as the strings get to large for simple NVARCHAR2 parameters.
Now, the way that we can make that work is to use large number of values that are stored on the database already; in the way the TB_LIST_ELEMENTS proposal works. The only question is how can the database be filled up with a large number of values quickly enough, so that the following query is fast? Persisting an object with a list of > 1000 entries requires >1000 rows to be generated. It depends on how often the list changes that this approach is viable.
The following code could then be used; it assumes a persitent type 'ManySimpleValues' in the database having a Guid Id and an ICollection<int> Values property.
Guid listId = ...
var q = context.Entities.Where(x => context.ManySimpleValues.Where(z => z.Id == listId).Any(z => z.Values.Contains(x.TheProperty)) );
Another completely different approach would be to define and call a stored procedure that returns persistent objects. In this sp you could perform any action.... manually.
Table valued parameters/functions would be a principally possible approach too, but they are not available on many databases and OpenAccess does not have support for them yet either.
In summary: The root problem is the transport of many values to the server, this is why servers have limitations in the amount of SQL or in the number of parameters or in the number of operands in the IN statement. If you avoid that, joins/exists/sp can be used to efficiently query the stored data.
The client side approach of splitting overlong lists into multiple sublists and issuing multiple queries and then concatenating the results would also be doable,but demands that certain restrictions on the kind of filters / orderings etc be met.
I would have liked to give a 'just do this or that'-answer, but as you can see, this is an issue where database limits are reached, and maybe a different approach is required to allow an efficient processing.
the Telerik team
Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!