%3CLINGO-SUB%20id%3D%22lingo-sub-509720%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20Get%20data%20from%20large%20external%20lists%20in%20SharePoint%202010%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-509720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20TECHNET%20on%20Mar%2005%2C%202013%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThis%20blog%20post%20is%20a%20contribution%20from%20Chanchal%20Jain%2C%20an%20engineer%20with%20the%20SharePoint%20Developer%20Support%20team.%3C%2FP%3E%0A%3CP%3ERecently%2C%20I%20was%20working%20on%20a%20requirement%20on%20how%20to%20find%20items%20using%20an%20identifier%20in%20external%20lists%20which%20are%20very%20large%20in%20terms%20of%20the%20number%20of%20items%20they%20display.%26nbsp%3B%20It%E2%80%99s%20similar%20to%20selecting%20a%20record%20in%20a%20table%20with%20a%20WHERE%20clause.%3C%2FP%3E%0A%3CP%3EJust%20in%20case%20you%20are%20new%20to%20external%20lists%20(which%20is%20highly%20unlikely)%2C%20check%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fee557243(v%3Doffice.14).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20this%20%3C%2FA%3E%20out.%3C%2FP%3E%0A%3CP%3EOnce%20the%20list%20is%20created%2C%20if%20you%20have%20less%20than%202000%20(default%20threshold%20value%20to%20throttle%20records%20from%20a%20database%20backed%20BCS%20model)%20items%2C%20you%20can%20use%20CAML%20query%20as%20you%20would%20query%20a%20normal%20list.%26nbsp%3B%20It%20would%20work%20the%20same.%26nbsp%3B%20But%20as%20soon%20as%20you%20have%20items%20more%20than%202000%2C%20CAML%20query%20will%20not%20return%20results.%3C%2FP%3E%0A%3CP%3EI%20searched%2C%20researched%20and%20leveraged%20resources%20available%20to%20me%20and%20came%20up%20with%20the%20following%20steps%20to%20accomplish%20this%20task%20in%20hand.%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EBefore%20performing%20these%20steps%20make%20sure%20you%20have%20all%20the%20configuration%20done%20and%20you%20are%20able%20to%20see%20the%20external%20list%20in%20SharePoint%20UI%20with%20all%20permissions%20and%20that%20you%20are%20able%20to%20query%20the%20list%20using%20CAML.%26nbsp%3B%20This%20will%20help%20ensure%20you%20have%20a%20good%20working%20set.%3C%2FLI%3E%0A%3CLI%3EEdit%20the%20external%20content%20type%20using%20SharePoint%20Designer.%3C%2FLI%3E%0A%3CLI%3EClick%20%3CSTRONG%3E%20Operations%20Design%20%3C%2FSTRONG%3E%20view%20to%20list%20all%20the%20methods%2Foperations%20defined%20on%20the%20external%20source.%3C%2FLI%3E%0A%3CLI%3EClick%20on%20the%20existing%20%3CSTRONG%3E%20ReadList%20%3C%2FSTRONG%3E%20operation%20or%20create%20a%20new%20one.%26nbsp%3B%20The%20choice%20is%20completely%20at%20the%20hands%20of%20the%20designer.%26nbsp%3B%20I%20prefer%20editing%20the%20existing%20one.%3C%2FLI%3E%0A%3CLI%3EClick%20%3CSTRONG%3E%20Next%20%3C%2FSTRONG%3E%20till%20you%20land%20on%20the%20page%20which%20says%20%3CSTRONG%3E%20Filter%20Parameters%20Configuration%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%0A%3CLI%3EClick%20%3CSTRONG%3E%20Add%20Filter%20Parameter%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%0A%3CLI%3EClick%20the%20link%20%3CSTRONG%3E%20(Click%20to%20Add)%20%3C%2FSTRONG%3E%20on%20the%20right%20pane.%3C%2FLI%3E%0A%3CLI%3EIn%20the%20%3CSTRONG%3E%20New%20Filter%20%3C%2FSTRONG%3E%20textbox%2C%20provide%20a%20filter%20name.%26nbsp%3B%20E.g.%2C%20%3CEM%3E%20SearchMyID%20%3C%2FEM%3E%20.%3C%2FLI%3E%0A%3CLI%3EBased%20on%20the%20type%20of%20the%20primary%20key%20of%20your%20external%20data%20source%2C%20you%20can%20select%20%3CSTRONG%3E%20Compare%20%3C%2FSTRONG%3E%20or%20%3CSTRONG%3E%20Wildcard%20%3C%2FSTRONG%3E%20filter%20types.%26nbsp%3B%20I%20had%20an%20integer%20data%20type%20as%20the%20primary%20key%20of%20the%20external%20data%20source%20(SQL%20database%20table)%2C%20so%20selected%20%3CSTRONG%3E%20Comparison%20%3C%2FSTRONG%3E%20filter%20type.%3C%2FLI%3E%0A%3CLI%3ESelect%20the%20operator%20as%20%3CSTRONG%3E%20Equal%20%3C%2FSTRONG%3E%20for%20equal%20comparison%20and%20the%20filter%20field%20you%20want%20to%20search%20on.%3C%2FLI%3E%0A%3CLI%3ELeave%20rest%20of%20the%20options%20to%20their%20default%20and%20hit%20%3CSTRONG%3E%20OK%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%0A%3CLI%3EMake%20sure%20you%20have%20a%20limit%20filter%20(%20%3CEM%3E%20you%20should%20have%20already%20configured%20this%20when%20creating%20this%20operation%20the%20first%20time%20%3C%2FEM%3E%20)%20set%20as%20default%20in%20order%20to%20display%20the%20list%20in%20SharePoint%20UI.%3C%2FLI%3E%0A%3CLI%3EClick%20%3CSTRONG%3E%20Finish%20%3C%2FSTRONG%3E%20.%26nbsp%3B%20Save%20the%20external%20content%20type%20and%20publish%20it%20again.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EThat%E2%80%99s%20it%20with%20respect%20to%20configuring%20the%20external%20content%20type%2C%20now%20comes%20the%20part%20where%20you%20use%20the%20filter%20in%20the%20code%20to%20query%20external%20list%20using%20CAML.%26nbsp%3B%20This%20approach%20works%20equally%20good%20in%20server%2C%20client%20and%20javascript%20SharePoint%20object%20models.%3C%2FP%3E%0A%3CP%3EI%E2%80%99ll%20skip%20the%20part%20where%20you%20create%20site%2Fweb%20objects%20and%20get%20the%20list%20instance.%26nbsp%3B%20Here%E2%80%99s%20a%20sample%20CAML%20query%20that%20let%E2%80%99s%20you%20query%20for%20items%20in%20the%20external%20content%20type.%3C%2FP%3E%0A%3CDIV%20id%3D%22codeSnippetWrapper%22%3E%0A%3CDIV%20id%3D%22codeSnippet%22%3E%3CVIEW%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CMETHOD%20name%3D%22%26quot%3B%26lt%3BThe%22%20name%3D%22%22%20of%3D%22%22%20the%3D%22%22%20read%3D%22%22%20list%3D%22%22%20method%3D%22%22%20which%3D%22%22%20we%3D%22%22%20edited%3D%22%22%20to%3D%22%22%20add%3D%22%22%20the%3D%22%22%20filter%3D%22%22%3E%22%26gt%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFILTER%20name%3D%22%26quot%3B%26lt%3BThe%22%20name%3D%22%22%20of%3D%22%22%20the%3D%22%22%20filter%3D%22%22%3E%22%20Value%3D'%7B0%7D'%2F%26gt%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FFILTER%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CQUERY%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CWHERE%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CEQ%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFIELDREF%20name%3D%22'%26lt%3BName%22%20of%3D%22%22%20the%3D%22%22%20filter%3D%22%22%20field%3D%22%22%3E'%20%2F%26gt%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CVALUE%20type%3D%22'Number'%22%3E%7B0%7D%3C%2FVALUE%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FFIELDREF%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FEQ%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FWHERE%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CVIEWFIELDS%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFIELDREF%20name%3D%22'Field1'%2F%22%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFIELDREF%20name%3D%22'Field2'%2F%22%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFIELDREF%20name%3D%22'Field3'%2F%22%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFIELDREF%20name%3D%22'Field4'%2F%22%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CFIELDREF%20name%3D%22'Field5'%2F%22%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FFIELDREF%3E%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FFIELDREF%3E%3C%2FFIELDREF%3E%3C%2FFIELDREF%3E%3C%2FFIELDREF%3E%3C%2FVIEWFIELDS%3E%3C%2FQUERY%3E%3C%2FMETHOD%3E%3C%2FVIEW%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EUpdate%20the%20attributes%20marked%20with%20%3CSTRONG%3E%20%26lt%3B%20%3CEM%3E%20some%20text%20%3C%2FEM%3E%20%26gt%3B%20%3C%2FSTRONG%3E%20in%20the%20CAML%20query%20sample%20above%20with%20the%20appropriate%20values.%26nbsp%3B%20E.g%2C%20%3CEM%3E%20%3CTHE%20name%3D%22%22%20of%3D%22%22%20the%3D%22%22%20read%3D%22%22%20list%3D%22%22%20method%3D%22%22%20which%3D%22%22%20we%3D%22%22%20edited%3D%22%22%20to%3D%22%22%20add%3D%22%22%20the%3D%22%22%20filter%3D%22%22%3E%20%3C%2FTHE%3E%3C%2FEM%3E%20in%20the%20above%20CAML%20query%20in%20my%20case%20is%20actually%20%3CEM%3E%20ReadList%20%3C%2FEM%3E%20.%26nbsp%3B%20Use%20the%20String.Format%20to%20pass%20in%20the%20filter%20ID.%26nbsp%3B%20That%20should%20be%20it.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EThis%20works%20efficiently%20on%20the%20tests%20I%20did%20against%20SQL%20table%20with%20more%20than%2025%2C000%20records.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EHope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-509720%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20TECHNET%20on%20Mar%2005%2C%202013%20This%20blog%20post%20is%20a%20contribution%20from%20Chanchal%20Jain%2C%20an%20engineer%20with%20the%20SharePoint%20Developer%20Support%20team.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-509720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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.

  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!