I am trying to learn BCS for use in an in-house application. To keep it simple at this point I am limiting myself to a single table even though the table has FK references for grouping and some one-to-one relations for columns that can have more than a single value.
On my test database the single table contains 25k rows but in production this is expected to grow to several million. While a user will never look at thousands of rows at a single time it is possible that could be working with hundreds of rows. (Or start with thousands and dynamically apply filters in order to drill down to the small subset that really need.)
My questions have to do with filtering and paging and how to display the list of rows. I understand that when the ECT is created the query is limited to 2000 rows in the return set. All of the examples and postings I have read say to "create filters to limit the number of rows returned" yet none of them really go into a practical way to use the filter. Almost all of the examples add one (sometimes two) wildcard or exact match filters to a field or two.
The article, http://lightningtools.com/blog/archive/2010/06/25/sharepoint-2010-external-list-paging-ndash-server-side.aspx, talks about server-side paging for external lists but there is a Paging filter that can be applied to the ECT. (I do have to mention that it does not seem to work for me because the external list will not display and there is an exception in the log regarding not uniqueidentifier conversion to int problem...)
The other question I have is how to properly display the list of rows and allow the user to apply filters. Creating an External List gives me the standard SharePoint View that allows me to apply filters via the dropdown in the column header. The problem is that I see no way to enter values for the filters I applied in the ECT. And where does the filter get applied when I select a value(s) from the column header dropdown?
Whereas the Business Data List Web Part gives me an area where I can type in values my for ECT filters and it displays the SharePoint gridview for further filtering. I also noticed that the Web part displays more columns than the External List. (It seems that my external list gridview is missing any column that has a uniqueidentifier data type.)
Any insight or links to concrete postings/examples are greatly appreciated.