Contains() in a Where clause fails on Oracle if the list contains more than 1000 elements

4 posts, 0 answers
  1. Matej
    Matej avatar
    5 posts
    Member since:
    Sep 2012

    Posted 12 Jun 2014 Link to this post

    Hello,

    using LINQ-to-SQL for an Oracle database, the following fails if itemIds contains more than 1000 elements:

    context.Items.Where(i => itemIds.Contains(i.IdItem)).ToList()

    This is because Oracle does not support more than 1000 elements in a "WHERE ... IN (...)" clause.

    I believe Telerik should handle this case internally, because building a working query is difficult from code. For example, OR-ing several Contains() calls with expression builders, or even using Oracle-specifing clauses that can handle this.

    Here are some variations of queries that make this possible: post on StackOverflow.

    Thanks,
    Matej
  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 12 Jun 2014 Link to this post

    Hello Matej,

    Thank you for your input.

    Indeed such limitations are imposed by the relational database servers. Regarding the bahaviour of Telerik Data Access in such situations, let me assure you that it is capable of executing bulk select statements entirely on the server. In this documentation article you can find the details about how the limitations are worked around and what queries are generated according to the scenario circumstances. Additionally, here is a blogpost that provides an example of the Contains() usage.

    I hope this helps. In case you have additional questions, do not hesitate to get back to us.


    Regards,
    Doroteya
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Matej
    Matej avatar
    5 posts
    Member since:
    Sep 2012

    Posted 13 Jun 2014 Link to this post

    Hello again,

    the Telerik version we're using is Q3 2013 SP1. Is this a new feature of Q1 2014?

    The following throws for me:

    var ids = Enumerable.Range(1, 1001);
    var stuff = context.Items.Where(i => ids.Contains(i.IdItem)).ToList();

    However, if the range has 1 element less, it works.

    The exception is:
    Oracle.DataAccess.Client.OracleException: ORA-01795: maximum number of expressions in a list is 1000
  5. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 13 Jun 2014 Link to this post

    Hi Matej,

    Thank you for your feedback.

    The special handling of the Contains() method is introduces in our LINQ support in version Q1 2014 SP1 of Telerik Data Access. Additionally, as an alternative approach when retrieving multiple objects based on their ID, you can also use the GetObjectsByKeys() method of the context.

    I hope this helps. If you more information is necessary, do not hesitate to get back to us.


    Regards,
    Doroteya
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top