First published on TECHNET on Mar 05, 2013
This blog post is a contribution from Chanchal Jain, an engineer with the SharePoint Developer Support team.
Recently, I was working on a requirement on how to find items using an identifier in external lists which are very large in terms of the number of items they display. It’s similar to selecting a record in a table with a WHERE clause.
Just in case you are new to external lists (which is highly unlikely), check this out.
Once the list is created, if you have less than 2000 (default threshold value to throttle records from a database backed BCS model) items, you can use CAML query as you would query a normal list. It would work the same. But as soon as you have items more than 2000, CAML query will not return results.
I searched, researched and leveraged resources available to me and came up with the following steps to accomplish this task in hand.
That’s it with respect to configuring the external content type, now comes the part where you use the filter in the code to query external list using CAML. This approach works equally good in server, client and javascript SharePoint object models.
I’ll skip the part where you create site/web objects and get the list instance. Here’s a sample CAML query that let’s you query for items in the external content type.
Update the attributes marked with < some text > in the CAML query sample above with the appropriate values. E.g, <The name of the Read List method which we edited to add the Filter> in the above CAML query in my case is actually ReadList . Use the String.Format to pass in the filter ID. That should be it.
This works efficiently on the tests I did against SQL table with more than 25,000 records.
Hope this helps!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.