HOW TO: Get data from large external lists in SharePoint 2010

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.

1. Before performing these steps make sure you have all the configuration done and you are able to see the external list in SharePoint UI with all permissions and that you are able to query the list using CAML.  This will help ensure you have a good working set.
2. Edit the external content type using SharePoint Designer.
3. Click Operations Design view to list all the methods/operations defined on the external source.
4. Click on the existing ReadList operation or create a new one.  The choice is completely at the hands of the designer.  I prefer editing the existing one.
5. Click Next till you land on the page which says Filter Parameters Configuration.
6. Click Add Filter Parameter.
7. Click the link (Click to Add) on the right pane.
8. In the New Filter textbox, provide a filter name.  E.g., SearchMyID.
9. Based on the type of the primary key of your external data source, you can select Compare or Wildcard filter types.  I had an integer data type as the primary key of the external data source (SQL database table), so selected Comparison filter type.
10. Select the operator as Equal for equal comparison and the filter field you want to search on.
11. Leave rest of the options to their default and hit OK.
12. Make sure you have a limit filter (you should have already configured this when creating this operation the first time) set as default in order to display the list in SharePoint UI.
13. Click Finish.  Save the external content type and publish it again.

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.

 <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>

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!

Comments

• Anonymous
  January 01, 2003
  Hello, Will this work with SharePoint online? Thanks Gwen

• Anonymous
  October 22, 2013
  Thanks for writeing this, Iv;e been strugling with this exact issue for a while now. This works great if I hard code the parameter values, but I need to be able to pull the values from a QueryString. I'm using  a filter called Modify_Day. I set mu filter to <Method Name="Read List"  >          <Filter Name="Modify_Day" Value="1" /> </Method> and it works fine, but I need to get the value from the queryString. Intellisence tells me that the <filter> tag has a Type attribute , so I tried <Filter Name="Modify_Day" Type="QueryString" /> But that obviously did not work. Any Ideas how i could do this?

• Anonymous
  October 22, 2013
  I got It... My Filters in BCS are named Filter_Modify_Day, Filter_Modify_Month and Filter_Modify_Year, I set My View up as <View DisplayName="..."> <XslLink>main.xsl</XslLink>        <Method Name="Read List"  >          <Filter Name="Filter_Modify_Year" Value="{PARM_MODIFY_YEAR}" />          <Filter Name="Filter_Modify_Month" Value="{PARM_MODIFY_MONTH}" />          <Filter Name="Filter_Modify_Day" Value="{PARM_MODIFY_DAY}" />        </Method>        <ParameterBindings>          <ParameterBinding  Name="PARM_MODIFY_MONTH"  Location="QueryString(mm)"  DefaultValue="0" />          <ParameterBinding  Name="PARM_MODIFY_DAY"  Location="QueryString(dd)"  DefaultValue="0" />          <ParameterBinding  Name="PARM_MODIFY_YEAR"  Location="QueryString(yyyy)"   DefaultValue="0"/>        </ParameterBindings> Now i can append ?mm=10,dd=21,yyyy=2013 to get a specific day. Again, Thanks for writing this! Russell

• Anonymous
  April 04, 2014
  Thanks for the blog. Works nice in simple cases, but with our current project the following two questions arose, and I couldn't find an answer on the Web.
  
  1) If I define multiple filter parameters when setting up the content type, I can link the filters with AND / OR operations. But in what precedence are they evaluated? Can I influence this ?
  
  For example, if I have the definition "Filter1 OR Filter2 AND Filter3, I assume this is evaluated as in the order of definition, i.e., ( (Filter1 OR Filter2) AND Filter3). Correct? But what if I needed (Filter1 OR (Filter2 AND Filter3))?
  
  2) Is there a possibility to pass a list of possible values to a Comparison filter? How would the CAML look like?
  
  Example: I have one table with employees, and a second table with training courses and how attended them. Now I want the get all employees (with all fields) from the Employee table that have attended a certain training course. What I would like to do is to first query the Training Course table to get a list of all the employees, and then pass that list (as filter parameter) to a query on the Employee table.
  
  Thanks,
  -Stephan

• Anonymous
  April 09, 2015
  Thanks so much! Your instructions are very clear and helpful. I've been wondering how to do this for months and thrilled to find out how finally!
  http://staygreenacademy.com/sharepoint-administrator-online-training-videos-certification-courses/">SharePoint 2013 Administrator Training Online

• Anonymous
  April 17, 2015
  Thanks!

• Anonymous
  February 18, 2016
  Hi,
  
  I have a external list in SharePoint 2010, and I'm trying to get multiple items from the list. Can anyone help me with this?
  
  vQuery.set_viewXml("" +
  "" +
  "" +
  "" +
  "1" +
  "2" +
  "" +
  "" +
  "");
  
  Can you please verify and let me know, whether it is correct or not?
  
  Thanks !!!

• Anonymous
  February 18, 2016
  
  
  
  
  
  
  
  
  1
  2