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.

Thank you.

  • 1
    I Agree. I am starting to think Sharepoint 2010 might be a waste of time. Our architects have been hawking it as a lightweight portal development platform to replace a lot of custom .net work for internal enterprise apps. But if external lists cannot page through external datasets via web service calls, then it is useless. I totally don't get this. Microsoft, IBM, Oracle and others all know this is where the world is going. Integration in the future is via web service, not direct database connection. IBM has a good portal based on web service integration now. Microsoft has the chance to stop u
    – user11425
    Commented Oct 17, 2012 at 19:42

2 Answers 2


I can only answer one part of this question at the moment. When you create an External List on your ECT, you can set the filter values for your ECT filter parameters if you go to List->Modify view->Filters. You can then enter your values for the list view here.


Getting BCS right is a tricky thing, and information on this seems really hard to come by, so I will do my best to point you in the right direction.

You create filters as part of the process for building your Read List operation. Although the wizard(if you use SPD to do this) will tell you you should create a Limit filter, you will be better served by creating some actual usable filters.

Think about how your users will interact with data. Chances are you have some filtering scheme in mind. For example, you may have millions of customers but for a particluar view you want to display only those in a certain zip code. In the wizard for your Read list operation you'll come to a page called Filter Parameters Configuration. Click the "Add Filter Parameter" button to add a filter. A little dialog will pop up where you can fill out the data. It also has a place for a default value, I would add a default value for each filter parameter you set up(you can set multiple filters as well).

A read List operation is analogous to setting up a view in a normal SharePoint list. When you set up filters on your "view", the view will only pull in the list items the default values of th efilters will allow. So if you don't set up these default values in your filter parameters, your list view will always appear to have no data.

In order to consume the filters you'll need to set up either a custom code-based web part(using the BDC API), or a custom Xslt List View, and set those filters based on a query string or some other technique. When using a list view, you can set up the filter consumers by clicking the "finder" button in the ribbon. The resulting wizard will detect your ECT filters and allow you to set up an XSL parameter(like a query string parameter) where you can hook everything up. The only thing now is to create some sort of control that will allow the user to select a zip code and load the page with the proper query string parameters.

  • ha, just noticed this question is five months old. Oh well, hopefully this will help somebody :) Commented Aug 1, 2012 at 20:01
  • Thank you for responding. We ended up abandoning the use of BCS for the project. It just ended up being too cumbersome and limited. The end-user was always going to be dealing with more than 2000 rows at a time and the filter would have been wildcard-ish so they could drill down on any columns tho the rows they were currently focused on...meaning I had to have paging. Another part of the problem (probably related to understanding) was getting a reusable component from my dev environment to the production environment. (I did not have the time to go the custom web part route..)
    – Jason
    Commented Oct 7, 2012 at 7:04
  • So when you abandoned BCS, what did you do as an alternative? I'm in the same boat, and cannot believe MS calls this a "product". MS Access for cryin out loud, can call > 2000 records from an external db.
    – Alan M
    Commented Aug 5, 2014 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.