Query string filtering for BCS data

Nowadays companies very rarely keep their data in one centralized source. Data is usually dispersed into many smaller data sources, served by different engines, based on data type and purpose, company organization, complexity and relations. It was always a typical case in this type of enterprises to write custom applications that gather and analyse data from different sources and present them as they were in the same place.

Introduction

Since SharePoint 2010 we have Business Connectivity Services, which are very helpful in retrieving and presenting data from external sources, making it look like the data is actually kept in SharePoint database (through external list).

When dealing with external data sources (especially large ones), we often experience performance issues. This is because SharePoint (by default) gets the whole amount of data from the data source every time user interacts with the external list. This means that we can filter the external list through UI, but filter operation occurs after the entire set of data is retrieved from the source. So what if we tell data source to pre-filter the result set before it is returned to SharePoint?

In this article we reveal some theory and show clever, practical way to setup filtering for external data sources in SharePoint 2013 through query string parameters to achieve significant performance improvement.

Introducing BDC

First of all let us explain what BDC actually is and what are its main goals and foundations.
Business Data Connectivity (often referred as BDC) is a service built into Microsoft SharePoint that enables to declaratively model external systems and to expose external data as a list in SharePoint sites.

The core function of BDC is to provide connectivity to external systems of different kinds with very little effort. BDC has built-in support for connecting to most common data providers:

• Databases (in general)
• Web services
• .NET assemblies

There is also a possibility to develop a custom connector plugin through Connector Framework (so in theory BDC service can connect to any data source).

The following diagram presents general concept of BDC:

At the foundation of Business Data Connectivity we have an object called BDC model, which is a bridge between SharePoint and external data source, describing how the connection is handled and what data is transferred between the two ends. In general, BDC model comprises of an entity and its methods:

• BDC entity describes the structure of business entity which is mapped to the real life business object (like customer, order etc.).
• Methods represent abstract descriptions of back-end API. A common task when working on BDC model is to create method instances that implement actual communication between external system and the model.

Method types

Full interface defining possible data operations for BDC model contains the following method types:

While complete two-directional communication between SharePoint and external data source will require implementing all methods, for simple one-way communication we will concentrate on Finder method, which returns list of all entities from external data source. We will configure this method to support filtering and to return only the subset of data we are interested in.

Creating sample BDC model

Visual Studio will make lots of work for us, so we can quickly setup a project and add model based on Business Data Connectivity Model template.

We make couple of quick changes in naming – feature name, Entity1 and Entity1Service should get more meaningful names (one thing to note – Visual Studio has already created Farm-scoped feature for us that will be used to deploy BDC model).

ExternalEntity class should be further changed to define set of metadata to be retrieved from external data source and to be presented to end users.

We will also provide a code to return a list of dummy external entities in ReadList method instance.

Testing BDC model

Now we can deploy our solution to SharePoint and test our BDC model.
To see the results we need to put Business Data List web part on the page and browse for our BDC model in web part settings.

If you don’t see your model in External Content Type Picker, most probably you’ve made a mistake in model configuration (ULS will help you to track down the problem). If everything is configured correctly, we should see data pulled from external data source with possibility to filter and sort as in normal SharePoint list view (this kind of filtering however happens on SharePoint side, so no matter what filter we choose in a list view, a whole list of entities is retrieved from external data source).

Making BDC model filterable

Now we will enable BDC model to be dynamically filtered on Category column.
First step is to define filter description for Category column (we can use Visual Studio designer to do that).

We give the filter descriptor a name (in our case it will be called categoryFilter). In Filter Field property we should say which property from ExternalEntity will be filtered (in our case it is called Category). We have different type of filters, but since we have a text property, we will use Wildcard filter.

The second step is to add input parameter to our ReadList method.

It is crucial here to bind type descriptor for parameter to filter descriptor created in previous step through Associated Filter property.

One last step is to adjust ReadList method to actually filter the results (note that category parameter has been added automatically to reflect changes made to the model).

Setting up filtering on the page

Once we have the latest version of our model deployed to the server, we can make additional setup on our page to actually enable filtering with the use of value provided in query string. To achieve that, we should put Query String (URL) Filter web part on the page and edit its properties.

Filter Name is the name of our choice, it is used only for identification. Query String Parameter Name is more important, because it says what key we will use in URL query string to provide value for filtering (for example http://pageUrl?category=Cat1).

Now we will connect Query String (URL) Filter web part to our Business Data List web part following these steps:

Our web parts are now connected, so we can append the query string to the page URL and see if filtering works as expected.

Summary and considerations

In this article I have shown how to configure DBC model to support filtering and how to setup the filtering on the SharePoint page. In your scenarios you should take into account the following considerations:

• There can be multiple filters applied at the same time (in this case ReadList method will have additional parameters and multiple Query String filter web parts will be connected to external list web part)
• This scenario supports different type of filters (so instead of Query String filter web part we can put on the page Text filter, Date filter or some other filter web parts)
• In our example ReadList method creates objects on the spot, but in real life scenario, in this method we should place the code that connects to external database or makes parametrized call to web service in order to get the results
• Many steps required for proper BDC model configuration (for example defining identifiers or creating type descriptors for entities) are intentionally omitted. This article concentrates on steps important from filtering point of view

Good luck with your external content types!