Request a large external list using CAML

Question

I have an external SharePoint list that points to a data table of 100,000 records. I have to set the filter in the read operation of the list, otherwise the list does not work. He will time out when he tries to return the full list. Therefore, I added a 200 Limit filter to the operation.

The problem is that when querying an external list using CAML, it searches only 200 returned records, not the complete list.

I would like it to look through the entire list, but only return 200 matching entries.

How can I achieve this?

Answer 1

Perhaps this answer will help SharePoint-Exchange. https://sharepoint.stackexchange.com/questions/31566/caml-and-external-list-pass-parameter-to-readlist-finder-method

My ideal is that you probably need to change your readlist method so that it reads the entire SQL table, but with the Where parameter specified by the filter parameter in the readlist method. Something like

Pseudocode:

public static IEnumerable<YourEntity> ReadList(string param) { if(string.IsNullOrEmpty(param) == true) { //Your original code thata only fetches the first 200 items } else { //Read your SQL-table with a Where ParamName = 'param' - clause } } 

Good luck.

Answer 2

Based on the structure of your request and the information presented here, reports <RowLimit> implements the desired functionality:

The RowLimit element sets the line limit for the view.

Syntax

The properties

• Paged: Optional boolean. TRUE if the list supports displaying more items across pages. If FALSE or unspecified, the line limit is absolute and there is no link to view more items.

Cautions: Pay attention to the notes in the documentation .

You have probably already checked this for your purposes (referring to your question: “So, I added a Limit filter of size 200 to the operation.”). So to the next question:

The problem is that when I request an external list, using CAML it searches only 200 returned records, not the complete list.

That seems weird. If you really use <RowLimit> and the documentation is correct:

The RowLimit element sets the row limit for the view.

And :

The <RowLimit> is in the definition of a direct child of representation and therefore cannot be nested inside the <Query> .

Then it must be fair that your subquery is executed in front of your View component to guarantee your limit. As a consequence of this, this should allow you to download results for the rest of the set defined by your request.

Based on these principles, we could build the so-called query:

 <View> <RowLimit Paged='True'>200</RowLimit> <Method Name='ReadList'/> <Query> <Where> <Contains> <FieldRef Name='Name'/> <Value Type='Text'>{0}</Value> </Contains> </Where> </Query> <ViewFields> <FieldRef Name='Name'/> <FieldRef Name='Id'/> <FieldRef Name='BdcIdentity'/> </ViewFields> </View> 

Noting, as stated in the documentation, that we should implement <PagedRowset> . If this is not desired, we set Paged='FALSE' above.

I'm probably not here at all, because it looks like what you have already tried. But in the interests of an exhaustive display of space, this cannot prevent it from being offered.

Answer 3

Unfortunately, this is a known issue with querying an external list. However, in OOB web parts, paging is supported using XSLT. RowLimit only works in XSLT, not in a CAML request. The external list does not have swap on the server side, and paging is the client side, which means that SharePoint pulls out all the data and then sets the filter limit in the view.

Answer 4

This is one of the scenarios in which there is no BCS code on it; in fact, it does not cut it. Either implement this as a stored procedure on the database server, or use the special BDC connector built into Visual Studio.

Answer 5

If you cannot pull the entire SQL table into an external list, then you cannot query this dataset as you could in a SharePoint list.

However, I can offer a solution that we used for scenarios that were almost identical to this, which worked very well for us. In our scenario, we query the Oracle database, which forever returns large data sets.

The approach we used was to use the Factory pattern to determine how the data source should be queried (SharePoint list, external database, etc.).

The following are examples, but they illustrate the concept well.

So, start with an interface that determines how the data set will be set and which fields will be returned:

 public interface IQueryData { string ListToQuery { get; set; } List<MyResultObject> ExecuteQuery(); } 

You will have a custom object representing a single record returned by the request

 public class MyResultObject { public string FileRef { get; } public string Title { get; set; } // any other fields you'd like to see potentially returned... } 

Then you will have a data provider that implements this interface for the SQL data source

 public class SqlDataProvider : IQueryData { public string ListToQuery { get { return "BigSqlTable"; } } public List<MyResultObject> ExecuteQuery() { // query your external data source here... // populate a list of MyResultObject from the result set and return it to the consumer } } 

You will also have a data provider that implements an interface for a SharePoint data source.

 public class SharePointDataProvider : IQueryData { public string ListToQuery { get { return "MySharePointList"; } } public List<MyResultObject> ExecuteQuery() { // query your SharePoint list here, using CAML, SharePoint object model, etc... // populate a list of MyResultObject from the result set and return it to the consumer } } 

In this implementation, you encapsulated the logic and details of the request in your respective data providers.

Now you will have a Factory, which builds the corresponding data provider (based on the specified ListToQuery parameter):

 public static class QueryDataProviderFactory { public static IQueryData Build(string listToQuery) { switch(listToQuery) { case "BigSqlTable": return new SqlDataProvider(); break; case "MySharePointList": return new SharePointDataProvider(); break; // you can have many other implementations here that query your data sources in different manners } } } 

Finally, you should use your Factory to initiate your request, passing in the name of the data source you want to request:

 public List<MyResultObject> RunQuery() { return QueryDataProviderFactory.Build("BigSqlTable").ExecuteQuery(); } 

This template keeps your external implementation encapsulated in its own data provider and abstracts the details of the request from the consumer. All the consumer needs to do is specify the name of the list they want to request, and Factory decides which implementation to initiate.

You can even make your IQueryData interface an implementation of generics for further extensibility:

 public interface IQueryData<T> { string ListToQuery { get; set; } List<T> ExecuteQuery(); } 

This will open the door for the consumer to also indicate the type of object they would expect to return.

There are many more members in our query data interface that add even more extensibility points to our query providers, but I thought this example illustrates the point in a concise and easy to understand form.

I just wanted to offer this proposal, because it looks like the same scenario that we encountered a year ago, and this strategy works very well for us.

Answer 6

Use ListItemCollectionPosition property from SPQuery and SPListItemCollection eg

 using (var web = site.OpenWeb("bla-bla")) { var list = web.Lists["your_list"]; var query = new SPQuery(); query.Query = "your query"; do { var items = list.GetItems(query); foreach(SPListItem item in items) { //your code } query.ListItemCollectionPosition = items.ListItemCollectionPosition; } while(query.ListItemCollectionPosition != null); }