Issue
Assume you have an External Content Type defined against a Data Source that contains a total of 3,000 items, but that has a limit filter of 1,000 items. You expose it as an external list in your SharePoint site. You want to query that external list, but using a standard SPQuery object to retrieve only seems to query your list against the first 1,000 items returned by the External Content Type. What you really want is for the query to go against the entire list of 3,000 items, but to return a total 1,000 max.
For example, assume your data source represents an inventory of cars, with information about the make and the color. If you were to query your external list for all green car from Honda using an SPQuery object, you may only get 30 records back, even if your inventory really contains 75. The reason for this is that the querying process first gets the first 1,000 items from the external list, then queries this 1,000 item dataset for all green cars.
Solution
Starting in SharePoint 2010, the object model introduced a new property on the SPQuery object named “Method”. This allows you dynamically the filters defined on any method defined by the External Content Type. In our case, we want to dynamically set the filter parameters of our Read List method.
In order achieve this, we will go and add a new Wildcard filter to the External Content Type on both the Make and Color columns of our data source. This will allow us to specify the values for these fields in our SPQuery object. Assuming our Read List operation on the External Content Type is named ReadCars and that our wildcard filters are respectively named CarColor and CarMake, our SPQuery object will be defined as follow:
SPQuery spQuery = new SPQuery();
spQuery.Method = @“<Method Name=’ReadCar’>
<Parameter Name=’CarColor’ Value=’Green’ />
<Parameter Name=’CarMake’ Value=’Honda’ />
</Method>”
Then we go and query our External List just like we would for an internal one using the query property:
SPList list = […];
SPListItemCollection greenCars = list.GetItems(spQuery);
What this really does in the background, is dynamically set the External Content Type filters and querying the entire data source using the values specified. Using the example above, would return all 75 items, and not the 30 items as if we would have used the query property instead.
