Getting Data from Large External Lists

Article
03/01/2013

Recently I was working on a requirement on how to find items using an identifier in External Lists which are very large. It’s similar to selecting a record in a table with a where clause.

If you want to know how to create an External List you can follow https://msdn.microsoft.com/en-us/library/ee557243(v=office.14).aspx

Once the list is created and if you have less than 2000 (the default threshold value to throttle records from a BCS data source), you can use CAML query as you do to query a normal list. It would work same as it works for any list.

But as soon as you have items that go beyond 2000 and you try to search for items that are not present positional in those 2000 records, the normal CAML List query will not return results.

I did search over for the best way to do that but could not find an easy way until I got help from Source Code & Escalation Services.

Here are the steps if you want to do or implement a Find method for your External Content Type:

Before doing these steps make sure you have all the configuration done and you are able to see the External List in the UI with all permissions and you are able to query the list using CAML. This will help to ensure we already have a working set.
Open the already created External Content Type for editing in SharePoint Designer.
Click on the Operations Design View to list all the methods/operations defined for the External Source.
Click on the existing “ReadList” operation or you can create a new one too. The choice is completely at the hands of the designer. I preferred editing the existing one.
Click “Next” till you land on the page which says “Filter Parameters Configuration”.
Click on “Add Filter Parameter”.
Click the link “(Click to Add)” on the right pane.
In the New Filter Text Box, give the filter a name. for example “SearchMyID”
Based on the type of the Primary Key for your External Data Source, you can select “Compare” or “Wildcard” in the filter type. For me I had an integer data type as my primary key for the External Data Source (SQL Table) and selected “Comparison” as the Filter type.
Select the Operator as “Equal” for equal comparison and the Filter field on which you want to search a record.
Keep the rest of the check boxes as it is and click “Ok”.
Make sure to have a Limit filter as your default filter to show the list in the UI with the records. (Which you would have already configured while creating the operation).
Click on Finish. And save the external content Type and publish again if required
This finishes the configuration of the External Content Type.

Now comes the part where you use the filter in the code to query the External List using CAML. The same works in SharePoint Server Object Model, SharePoint ClientSide Object Model, SharePoint Server JavaScript Model.

I’ll skip the part where you create Site and Web object and get the list instance. I’ll tell you the CAML query that needs to be used to use the filter which we created to search a record in the External Content Type.

The CAML query format is as below:

 <View>

  <Method Name="<The name of the Read List method which we edited to add the Filter>">

    <Filter Name="<The name of the Filter>" Value='{0}'/>

  </Method>

  <Query>

    <Where>

      <Eq>

        <FieldRef Name='<Name of the Filter Field>' />

        <Value Type='Number'>{0}</Value>

      </Eq>

    </Where>

  </Query>

  <ViewFields>

    <FieldRef Name='Field1'/>

    <FieldRef Name='Field2'/>

    <FieldRef Name='Field3'/>

    <FieldRef Name='Field4'/>

    <FieldRef Name='Field5'/>

  </ViewFields>

</View>

The CAML query above needs to be updated with the values that I have marked using “< Some text >”.

For example “The name of the Read List method which we edited to add the Filter” needs to be updated with “ReadList” in my case.

You can use String.Format to pass the filter id ( which is shown as {0} in the query ) for the above CAML query.

I have put snapshots below for clarity:

Read List Method Name : The name is highlighted in the snapshot

Name of the Filter: The value in the text box “New Filter” i.e. SearchMyID

Name of the Filter Field: The value in the Filter Field Dropdown i.e. “PrimId”

This works in an efficient manner and I have tested it for a SQL table with more than 25 k records.

Comments

Anonymous
November 09, 2013
Though I have a question, is it possible to do the same with lists.asmx providing the same caml query?
Anonymous
June 06, 2014
Thanks - this has solved a problem which I have been struggling with for a while now.
Anonymous
July 30, 2014
Do you really need the Query clause when you have the method and filter? I would think that the Query part was done after the data pull from SQL, which is filtered server-side using the filter. Just wondering thanks
Anonymous
July 02, 2016
Nice article, this helps one of issue resolution.
- Anonymous
  May 09, 2017
  Hi Amjad,I have created an external list and able to see the data but only 2000 records. Could you please let me know where should I use the CAML query mentioned above. Pleas help me to resolve the issue.Thanks Girish.
Anonymous
May 05, 2017
Hi Chanchal,I have created an external list and able to see the data but only 2000 records. Could you please let me know where I should you the CAML you mentioned above. Pleas help me to resolve the issue.ThanksGirish
Anonymous
September 12, 2017
Thanks,This has solved my problem. Now we can query the list & get the data.
Anonymous
October 14, 2017
I tried the approach but some how its not working i have more than 25k records in exernal table.can you help me in this..Here is my code.var viewXml = { ViewXml: ""+ ""+ ""+ ""+ ""+ ""+ ""+ ""+ "0000000001"+ ""+ ""+ ""+ ""+ ""+ ""+ ""+ ""+ ""};var call = jQuery.ajax({ url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getByTitle('tbl_Recent_Stock')/GetItems(query=@v1)?" + "@v1=" + JSON.stringify(viewXml), type: "POST", dataType: "json", headers: { Accept: "application/json;odata=verbose", "X-RequestDigest": jQuery("#__REQUESTDIGEST").val() }});call.done(function (data, textStatus, jqXHR) { debugger;});call.fail(function (jqXHR, textStatus, errorThrown) { console.log(JSON.parse(jqXHR.responseText).error.message.value); debugger;});
- Anonymous
  October 14, 2017
  ViewXml: ""+ ""+ ""+ ""+ ""+ ""+ ""+ ""+ "0000000001"+ ""+ ""+ ""+ ""+ ""+ ""+ ""+ ""+ ""
  - Anonymous
    October 14, 2017
    Some how unable to paste my code. ViewXml: ""0000000001"

Share via

Getting Data from Large External Lists

Comments

Additional resources