How to Add Filtering to Queries

You can add filters in the calls to the service using the $top parameter.

Prerequisites

Make sure that the OData service supports filtering for the specific property.

Context

Filters are important if you want to limit the results retrieved by the service based on the selected filter criteria, and to enhance the performance of your back-end system.

Use the method, WCF EF AddQueryOption, to pass the system query name ($filter in our case), and the filter condition.

Note $top option code is generated by default, and available if the handling of $top has been done on the server side.

Procedure

Inserting Filters

Context

To insert a filter:

Procedure

From the Solution Explorer, open the class, Excel ExcelManager ExcelDataManager.cs

Go the method, BindExcelTableWithRecords.

Add comments to the line:

//var serviceresponse = serviceContext.SalesOrderSet.AddQueryOption("$top",ExcelSettingManager.MaxRecords).Execute() as QueryOperationResponse<ZTEST_SEPM_SRV.SalesOrder>;

Insert a new line below the commented line for the code snippet below.

Insert the following:

var serviceresponse = serviceContext.SalesOrderSet.AddQueryOption("$top", ExcelSettingManager.MaxRecords).AddQueryOption("$filter", "SoId gt '500000223'").Execute() as QueryOperationResponse<ZTEST_SEPM_SRV.SalesOrder >;

Rebuild and run the project for your Excel add-in application.
From Microsoft Excel, click on the Fetch button, and check that the results are displayed according to the filter.

Combining Filter Conditions Using the OR Operator

Context

To combine filter conditions using the OR operator:

Procedure

From the Solution Explorer, open the class, Excel ExcelManager ExcelDataManager.cs .

Go to the method, BindExcelTableWithRecords.

Add a comment to the line:

 //var serviceresponse = serviceContext.SalesOrderSet.AddQueryOption("$top",ExcelSettingManager.MaxRecords).Execute() as QueryOperationResponse<ZTEST_SEPM_SRV.SalesOrder>;

Insert a new line below the commented line for the code snippet below.

Insert the following:

 var serviceresponse = serviceContext.SalesOrderSet.AddQueryOption("$top", ExcelSettingManager.MaxRecords).AddQueryOption("$filter", "SoId gt '500000239' or SoId eq '500000223'").Execute() as QueryOperationResponse<ZTEST_SEPM_SRV.SalesOrder>;

Rebuild and run the project for Excel add-in application.
In Microsoft Excel, click on the Fetch button, and check that the results are displayed according to the filter.
For more information on how to build filter expressions, see http://www.odata.org/documentation/uri-conventions/#SystemQueryOptions.

Inserting Filter Values Using the Configuration File

Context

Alternatively, you can add the filter values into the App.config file in order to enable application flexibility.

To add filter values into the configuration file and implement it in the generated code:

Procedure

From the Solution Explorer, open the configuration file, <Your Excel Project Name> SAP Service Reference App.config .
Add the new configuration for a filter: <add key="filterSoID" value="'500000223'"/>.
From the Solution Explorer, open the class, Excel ExcelManager ExcelDataManager.cs
Go the method, BindExcelTableWithRecords.
Add the following code to read the configurations from the App.config file:
```
 string myfilter = ConfigurationReaderHandler.Instance.GetConfigValue("filterSoID"); 
```

Add a comment to the line as shown below.

//var serviceresponse = serviceContext.SalesOrderSet.AddQueryOption("$top",ExcelSettingManager.MaxRecords).Execute() as QueryOperationResponse<ZTEST_SEPM_SRV.SalesOrder>;

Insert a new line below the commented line for the code snippet below.

Insert the following

var serviceresponse = serviceContext.SalesOrderSet.AddQueryOption("$top", ExcelSettingManager.MaxRecords).AddQueryOption("$filter", string.Format("SoId gt {0}", myfilter)).Execute() as QueryOperationResponse<ZTEST_SEPM_SRV.SalesOrder>;

Rebuild and run the Excel add-in project.
In Microsoft Excel, click on the Fetch button, and check that the results are displayed according to the filter configurations.