Query large external list with CAML

Question

I have a SharePoint external list that points to a 100,000 record SQL table. I have to set a filter on the Read List Operation otherwise the list doesnt work. It will timeout as it tries to return the full list. So I have added a Limit filter of size 200 onto the operation.

THe problem this causes is that when I query the external list using CAML it only searches the 200 entries returned, not the full list.

I would like it to search the entire list, but return only a maximum of 200 matching entries.

How can I best achieve this?

Answer 1

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

My idead is that you will probably need to modify your readlist-method to make sure that it reads the entire SQL-table but with the Where-parameter specified by the filter parameter in the readlist-method. Something like

Pseudo code:

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 upon the structure of your query and the information presented here, reports indicate <RowLimit> implements the functionality you desire:

The RowLimit element sets the row limit for a view.

Syntax

Attributes

• Paged: Optional Boolean. TRUE if the list supports displaying more items page by page. If FALSE or unspecified, then the row limit is absolute and there is no link to see more items.

Caveats: note the remarks in the documentation.

You've probably already inspected this for your purposes (citing your question: "So I have added a Limit filter of size 200 onto the operation."). So, on to the next issue:

The problem this causes is that when I query the external list using CAML it only searches the 200 entries returned, not the full list.

This seems strange. If you're indeed using <RowLimit> and the documentation is correct:

The RowLimit element sets the row limit for a view.

And:

The <RowLimit> tag is in the schema definition of a view (direct child of ) and therefore cannot be nested inside a <Query> tag.

Then it should hold true that your nested query executes before your View component to satisfy the guarantee of your limit statement. As a corollary to this, this should allow you to perform results paging across the rest of the set defined by your query.

Building on these principles, we might construct a paged query like this:

<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 mentioned in the documentation, that we must implement <PagedRowset>. If this isn't desired, we set Paged='FALSE' above.

I'm probably entirely off base here, because this seems like exactly what you've tried already. But, in the interests of exhaustively mapping out the space, it can't hurt to suggest it.