Getting Data from Large External Lists

  • Article

 

 

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:

  1. 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.
  2. Open the already created External Content Type for editing in SharePoint Designer.
  3. Click on the Operations Design View to list all the methods/operations defined for the External Source.
  4. 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.
  5. Click “Next” till you land on the page which says “Filter Parameters Configuration”.
  6. Click on “Add Filter Parameter”.
  7. Click the link “(Click to Add)” on the right pane.
  8. In the New Filter Text Box, give the filter a name. for example “SearchMyID”
  9. 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.
  10. Select the Operator as “Equal” for equal comparison and the Filter field on which you want to search a record.
  11. Keep the rest of the check boxes as it is and click “Ok”.
  12. 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).
  13. Click on Finish. And save the external content Type and publish again if required
  14. 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

image

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”

image

 

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