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
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).
Provide your SharePoint URL where you want to host your App and select SharePoint-hosted:
After the project is created, add a Content Type for an External Data Source:
Select your OData Service Url (in this sample it’s http://services.odata.org/Northwind/Northwind.svc/):
Select the data entities you want to include into your model
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
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:
<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:
External Content Type / Model
When you click on the Order.ect the External Content Type designer opens:
By default a Limit filter is generated which limits the rows retrieved from the server to 100 rows:
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.
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”
select Comparison as Filter Type and select the column you like – in my case it’s ShipCity and press OK
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
select XML (Text) Editor
OK and Yes again.
|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:
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:
Now lets select a filter value a see what happens:
The runtime replaces the @ShipCity parameter
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:
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?
Deploy it and test it:
Select a value and look at the result, 5 rows are returned:
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!
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’
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.
<Property Name="ODataEntityUrl" Type="System.String">/Orders?$filter=@ShipCity and @ShipName&amp;$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!
A closer look at the issued query shows that an orderby clause was generated:
But what is this $skiptoken? We didn’t provide it!
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!
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