This is a migrated thread and some comments may be shown as answers.

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

3 Answers 606 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Matej
Top achievements
Rank 1
Matej asked on 12 Jun 2014, 12:14 PM
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

3 Answers, 1 is accepted

Sort by
0
Doroteya
Telerik team
answered on 12 Jun 2014, 02:30 PM
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.
 
0
Matej
Top achievements
Rank 1
answered on 13 Jun 2014, 08:56 AM
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
0
Doroteya
Telerik team
answered on 13 Jun 2014, 12:43 PM
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.
 
Tags
LINQ (LINQ specific questions)
Asked by
Matej
Top achievements
Rank 1
Answers by
Doroteya
Telerik team
Matej
Top achievements
Rank 1
Share this question
or