Paging and sorting with BCS - Is it really possible?

Question

I'm trying to add paging on an external list and so far it does not work 100%. The best references I could find (that actually... kinda... work) are these:

Paging and sorting with BCS

Paging and sorting in sharepoint BCS

I've done like in the posts (I have a .Net Assembly Type Lob System instead of WCF) and all works if I set constant values for the filters in the view. But if I use name placeholders (like {dvt_sortdir}) I get errors that the value {dvt_sortdir} can't be converted to System.Int32.

<View ...>
  <Method Name="GetEmployeesPaged">
     <Filter Name="FilterRowNum" Value="{dvt_firstrow}"/>
     <Filter Name="FilterPageCount" Value="30"/>
     <Filter Name="FilterSortColumn" Value="{dvt_sortfield}"/>
     <Filter Name="FilterSortDir" Value="{dvt_sortdir}"/>
  </Method>
  ...
</View>

In the second post I noticed it's {$dvt_sortdir} but again it does not work (recreating the list is still no joy).

I've tried all sorts of combinations in SP Designer but nothing works, just plain number. But I don't need constant numbers, I need values at runtime.

I've read a lot and tried a lot and I'm starting to doubt that paging is possible on an external list. So is it possible or not? What's missing from those posts to make it work?

Answer 1

Struggled with this quite a bit myself. You need to check the parameter types of your finder methods. Check both the code as well as the model file. Those should match and be of the right type (depends on the parameter; firstrow and pagecount should be integers).

As far as I know, you cannot use filter parameters which don't exist in your entity. So you need to abuse fields in your entity for filtering. Those fields have to be of the correct type. So if your entity has an integer column, you could use that as pagenumber parameter; make sure you properly comment your code because you'll lose track of things when you don't.

As for sorting, I didn't get that working at all. My solution performs sorting, but only on the items retrieved from BCS (which are paginated). So first it's retrieving items from my BCS connector, which only returns like 30 records, and THEN it sorts those records. Kind of useless. Perhaps you could get there with dvt_sortfield and dvt_sortdir, but that would require two more (string?) parameters in your finder method (and thus entity).

I found this link pretty usefull: http://lightningtools.com/blog/archive/2010/06/25/sharepoint-2010-external-list-paging-ndash-server-side.aspx