SharePoint 2013 External List Enhancements with OData: Sorting and Filtering

Created: December 2012
Summary: How to create BDC Model with enhanced Filtering, Sorting and paging for external lists.
Applies to: SharePoint 2013
Provided by: Benedikt Redl

Overview

With OData and the External List Enhancements SharePoint 2013 offers a powerful way to retrieve sorted and filtered data from OData sources. When a user clicks in a column header in an external list the filter and sort criteria can be passed to the server and only this data is given back.

Generate the BDC-Model

A good starting point is to generate the model in Visual Studio 2013 by creating an App. (You can also just follow the steps in How to: Create an external content type from an OData source in SharePoint 2013).

When you have created your model you can easily use this as a farm model and create external list based on it (link at the end of the blog).

In Visual Studio 2012 create a new App for SharePoint 2013 Project:clip_image002

Provide your SharePoint URL where you want to host your App and select SharePoint-hosted:

clip_image004

After the project is created, add a Content Type for an External Data Source:

clip_image006

Select your OData Service Url (in this sample it’s http://services.odata.org/Northwind/Northwind.svc/):

clip_image008

Select the data entities you want to include into your model

image

And press Finish. Visual now generates the external list(s) and external content type(s).

A Link for easier Navigation

If you create this App only to evaluate external lists, you can add a link to your external list which is not created by default. This is for easier navigation only and has nothing to do with BCS and OData.

Just look at external list Elements.xml 

clip_image010

and take the ListInstance Url-Parmeter value together with the name of the App and put it in a Hyperlink in the Apps default.aspx:

clip_image012

<asp:Content ContentPlaceHolderID="PlaceHolderMain" runat="server">
	<div>
		<p id="message">
			<!-- The following content will be replaced with the user name when you run the app - see App.js -->
				initializing...
		</p>
		<asp:HyperLink ID="HyperLink1" NavigateUrl="/ODataOrdersApp/Lists/Orders" runat="server">Orders External List</asp:HyperLink>
	</div>
</asp:Content>

It will render a link to your external list without the need to change the Url in the browser:

image

 

External Content Type / Model

When you click on the Order.ect the External Content Type designer opens:

clip_image014

By default a Limit filter is generated which limits the rows retrieved from the server to 100 rows:

clip_image015

With the current version of the designer, you can not modify the limit, you have to delete and recreate it if you want to change it. In my sample, I delete it, because I do not want to limit my result set through a limit filter.
clip_image016
Just select the row and right click on the green arrow and click delete in the context menu.

Add a OData Filter to your model

To add a OData Filter, you just need to add a Comparison Filter through the designer. Click into the filter box to “Click here to add a filter”

clip_image018

select Comparison as Filter Type and select the column you like – in my case it’s ShipCity and press OK

image

Now lets have a look what the designer had generated. To do this, lets switch to the XML view of the external content type.

Right-Click on the Orders.ect file, select Open With

clip_image020

yes

clip_image021

select XML (Text) Editor

clip_image022

OK and Yes again.

clip_image023

Attention: if you make changes with the XML (Text) Editor and you switch back to the ETC Designer and make modifications there you may loose your changes!

The part where the filter comes into place is the ReadAllOrder-Method:

<Method Name="ReadAllOrder" DefaultDisplayName="Read All Order" IsStatic="false">
  <Properties>
    <Property Name="ODataEntityUrl" Type="System.String">/Orders?$filter=@ShipCity</Property>
  </Properties>
  <FilterDescriptors>
    <FilterDescriptor Name="ShipCityFilter" DefaultDisplayName="ShipCityFilter" Type="Comparison" FilterField="ShipCity">
      <Properties>
        <Property Name="Comparator" Type="System.String">Equals</Property>
      </Properties>
    </FilterDescriptor>
  </FilterDescriptors>
  <Parameters>
    <Parameter Name="@ShipCity" Direction="In">
      <TypeDescriptor Name="ShipCity" DefaultDisplayName="ShipCity" TypeName="System.String" AssociatedFilter="ShipCityFilter">
        <Properties>
          <Property Name="ODataFilterUrl" Type="System.String">ShipCity eq '@ShipCity'</Property>
          <Property Name="LogicalOperatorWithPrevious" Type="System.String">And</Property>
          <Property Name="Order" Type="System.String">0</Property>
        </Properties>
      </TypeDescriptor>
    </Parameter>

The first thing to mention is that the ODataEntityUrl is now extended by a OData $filter System Query Option: $filter=@ShipCity

@ShipCity will be replaced by the value the user selects in external list column filter. So that the BDC runtime can replace this parameter a FilterDescriptor with Type=”Comparison” and the FilterField=”ShipCity” with a Parameter definition is also necessary and generated.

Deploy the App and display the list:

image

The log shows the entries:
image

Well, we didn’t select any filter value and $filter=true return all rows, as a quick test with a OData call in the browser also shows:

image

Now lets select a filter value a see what happens:

image

The runtime replaces the @ShipCity parameter

image

and only 6 rows are returned from the LobSystem as a short check with the browser shows:
image

Be aware of the column data type!

The filtering works well with string types, but with other data types you will run into problems when you do not define filter values. Maybe there are additional parameters for the FilterDescriptor to control this behavior, but currently I don’t know any.

If you for example take EmployeeID for the comparison filter and no filter value is defined, SharePoint does not replace the @EmployeeID with $filter=true, instead it replaces it with  $filter=EmployeeID eq 0 with leads to zero result rows:

clip_image025

A similar result is with Date data types like OrderDate which is replaced like $filter=OrderDate eq datetime’1900-01-01T00:00:00.000′

Multiple Filter Parameters

What happens when you add more than one filter parameter?

Well lets add an additional filter with a string data type: ShipNameclip_image027

Deploy it and test it:

clip_image029

Select a value and look at the result, 5 rows are returned:

clip_image031

The filter URL from the model $filter=@ShipCity&@ShipName is now replaced to $filter=true&ShipName eq ‘Alfred”s%20Futterkiste’

A short check with the browser shows, that there are 200 rows retrieved from the server, not 5!

clip_image033

A look into the OData specification at http://www.odata.org/media/30002/OData%20ABNF.html shows, that the filter expression is not generated correctly. Instead of “&” there must be and “and”.

The correct filter URL is: $filter=true and ShipName eq ‘Alfred”s%20Futterkiste’

A short check with the browser shows, that this change shows up the right result.clip_image035

Sort Filters

Sort filter are currently not supported by the ETC-Designer, which means you have to add it in the XML (Text) Editor manually.

Like the Comparison filter you have to extend the ODataEntityUrl, add a FilterDescriptor and add a Parameter to the ReadAll-Method.

According to the OData URL Conventions a $orderby System Query Option has to be appended to the ODataEntityUrl: $orderby=@OrderByToken

 <Property Name="ODataEntityUrl" Type="System.String">/Orders?$filter=@ShipCity and @ShipName&$orderby=@OrderByToken</Property>

A Sorting filter has to defined:

<FilterDescriptor Type="Sorting" Name="SortFilter"></FilterDescriptor>

and a SortParameter, which has the IsSortInput attribute set to true, that defines the columns which should be sortable at server side:

<Parameter Direction="In" Name="@OrderByToken">
  <TypeDescriptor TypeName="System.String" IsSortInput="true" AssociatedFilter="SortFilter" Name="SortParameter" >
    <DefaultValues>
      <DefaultValue MethodInstanceName="ReadAllOrder" Type="System.String">
        <![CDATA[<Sort>
                    <Param Name="OrderID" Ascending="false"/>
                    <Param Name="ShipName" Ascending="true"/>
                    <Param Name="ShipCity" Ascending="true"/>
                  </Sort>]]>
      </DefaultValue>
    </DefaultValues>
  </TypeDescriptor>
</Parameter>

Don’t forget to define all sortable columns as Sortable e.g. OrderID

<TypeDescriptor Name="OrderID" DefaultDisplayName="OrderID" TypeName="System.Int32" IdentifierName="OrderID" ReadOnly="true">
  <Properties>
    <Property Name="Sortable" Type="System.Boolean">true</Property>
  </Properties>
</TypeDescriptor>

Define the same for ShipName and ShipCity!

Deploy the model and set sort the column:clip_image037

A closer look at the issued query shows that an orderby clause was generated:
http://services.odata.org/Northwind/Northwind.svc/Orders?$filter=true%20and%20true&$orderby=ShipCity%20asc,OrderID%20asc&$skiptoken=’Toulouse’,11051,11051

But what is this $skiptoken? We didn’t provide it!

Paging

Well our result is 200 rows long and we now have a sorted result.
The definition of $skiptoken (http://www.odata.org/media/16352/[ms-odata].pdf) means that we have now paging – automatically OOB without any additional definitions!

clip_image039

A short check with the browser shows, that only 30 rows (the default pagesize) are retrieved from the server:clip_image041

 

Conclusion

The new list enhancements are a real enrichment of the BCS functionality. They allow sorting, filtering and paging on data source side without any coding.

As long as no new version of the Visual Studio Tools for SharePoint and additional documentation of OData BDC models are released, the following approach to build up the model seams to make sense:

1. Create a App for SharePoint 2013 and add Content Types for an External Data Source
2. Add all filter parameters (that means Comparison Filters) with the ECT-Designer
3. Switch to the XML (Text) Editor and correct the $filter expression
4. Be carefully with data types!
5. Add the sorting filters manually
6. Deploy the App

If you want to use the model in a Farm solution please take a look at my previous blog External list using an OData Source in SharePoint 2013 based on a Farm wide BCD Model

Advertisements
Posted in BCS, OData | Leave a comment