Is there any reason System.Data.Filter.ExpressionParser to be limited to 100/50 conditions?

[TrayanZapryanov]

I've found following restriction while using DataTable.Select(...) functionality : filter limit.
Before limiting in our software, I would like to ask if this was introduced due to a technical reason or just to have some limitation.
One our clients build really strange SQL statement(maybe because of our not ideal UI) and it looks like this :
(([RUTA]='003') OR (([RUTA]='004') OR (([RUTA]='013') OR (([RUTA]='048') OR (([RUTA]='060') OR (([RUTA]='043') OR (([RUTA]='018') OR (([RUTA]='044') OR (([RUTA]='065') OR (([N__CLIENT]='7258') OR (([N__CLIENT]='4381') OR ((([DOCUMENTNO] LIKE '201911%')) OR ((([DOCUMENTNO] LIKE '202011%')) OR ((([DOCUMENTNO] LIKE '202111%')) OR (([RUTA]='066') OR (([RUTA]='067') OR (([RUTA]='068') OR (([DOCUMENTNO]='202211') OR (([DOCUMENTNO]='202311') OR (([DOCUMENTNO]='202411') OR (([DOCUMENTNO]='202511') OR (([DOCUMENTNO]='202611') OR (([RUTA]='049') OR (([RUTA]=':047') OR (([RUTA]=':005') OR (([RUTA]=':008') OR (([RUTA]=':007') OR (([RUTA]=':002') OR (([RUTA]=':006') OR (([RUTA]=':003') OR (([RUTA]=':004') OR (([RUTA]=':013') OR (([RUTA]=':048') OR (([RUTA]=':060') OR (([RUTA]=':043') OR (([RUTA]=':018') OR (([RUTA]=':044') OR (([RUTA]=':065') OR (([RUTA]=':066') OR (([RUTA]=':067') OR (([RUTA]=':068') OR (([RUTA]=':049') OR (([N__CLIENT]='4112') OR (([N__CLIENT]='6733') OR (([N__CLIENT]='20') OR (([N__CLIENT]='304') OR (([N__CLIENT]='1710') OR (([N__CLIENT]='1565') OR (([N__CLIENT]='1643') OR ([N__CLIENT]='6742'))))))))))))))))))))))))))))))))))))))))))))))))))

and it result in following exception:

[TrayanZapryanov]

Callstack:
System.IndexOutOfRangeException
HResult=0x80131508
Message=Index was outside the bounds of the array.
Source=System.Data
StackTrace:
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
at System.Data.Select..ctor(DataTable table, String filterExpression, String sort, DataViewRowState recordStates)
at System.Data.DataTable.Select(String filterExpression, String sort)

[Clockwork-Muse]

Even ignoring the limit here, all SQL databases (and the drivers used to communicate with them) impose limits on tokens (ie, each OR and comparator) and total statement length. So at some point you're going to hit this limit at a lower level. There are technical reasons for this.

For your specific substatement shown here, if your database supports it you may be able to use some form of array or table parameter (ie, like an IN statement), which would reduce the number of comparators.

[TrayanZapryanov]

@Clockwork-Muse Thank you for the replay.
Unfortunately, our UI is not good enough in allowing multiple values to be entered in a list. We will definitely work on this in future versions. For the database we are using Azure SQ, so I guess we will not hit soon it's limit(it was 1000 I think). As DataTable is a memory structure, I still cannot find any good reason for limiting only to 100.

[Clockwork-Muse]

(Current versions of) Azure SQL might have a limit of 1000. Older/other databases do not.

[TrayanZapryanov]

@Clockwork-Muse I will change code in our software, just for my understanding - if DataTable class where this filtering is used is pure memory structure, why we need this limitation? This class for parsing "where" clause is not used at other places and can have much higher limit. Also conditions built from it are not transferred to any real database engine(or I am wrong), so we should not align with others.

[Clockwork-Muse]

Sometimes the exact limit on a restriction is just arbitrary. You have to have one for performance reasons.
You're right that DataTable is a wholly in-memory representation, so it's not going to hit a database (or its limits).

[TrayanZapryanov]

Thank you.