CODEWITHSUNDEEP
asp.net-web-apikendo-gridodatakendo-asp.net-mvc
Ryan Mrachek
Ryan Mrachek

Reputation: 771

Odata v4 working with Kendoui grid contains filter

What is easiest way to get the contains filter working with a asp.net web api web service that uses odata v4?

It seems that web services using odata v4 no longer acknowledge the 'substringof' function and want the ' contains' function.

Example: GET using the Contains filter on the WorkUnitCode column in the grid and entering 'xYz'. substringof(fails)

http://localhost:1486/odata/BillOfMaterials(2)/BillOfMaterialsItems?$format=json&$top=10&$filter=substringof('xYz',WorkUnitCode)&$count=true

What the GET needs to be for the contains function to work:

http://localhost:1486/odata/BillOfMaterials(2)/BillOfMaterialsItems?$format=json&$top=10&$filter=contains(WorkUnitCode,'xYz')&$count=true

I believe there are two ways to approach this problem and not sure which is better or how given either solution is reusable.

Approach 1: Intercept the request and change it to use the contains function with reversed parameters. Approach 2: Add the substringof functionality to the web api.

Upvotes: 3

Views: 6300

Answers (6)

Alexander Shagin
Alexander Shagin

Reputation: 505

Another one bit improved example of the @Ryan Mrachek's answer:

// Let's use 'indexof' instead of 'substringof'
function substringofToIndexof(s) {
  s = s.substring(s.indexOf("("));
  s = s.substring(1, s.length - 1);
  const p = s.split(",");
  // Note: TypeScript syntax below
  return `indexof(${p[1]},${p[0]}) ge 0`;
}
...
parameterMap: (options, operation) => {
  const paramMap = kendo.data.transports.odata.parameterMap(options, operation);
  if (paramMap.$filter.startsWith("substringof")) {
    paramMap.$filter = substringofToIndexof(paramMap.$filter);
    return paramMap;
  }

Using Kendo UI With MVC4, WebAPI, OData And EF

Supporting OData Query Options in ASP.NET Web API 2

Using Filter Expressions in OData URIs

Upvotes: 0

nyxthulhu
nyxthulhu

Reputation: 9752

Update Since Kendo now supports ODATA V4 there is no longer any need for tweaks to make it work.

Changing the data set type from

type: 'odata'

to

type: 'odata-v4'

Should do the trick. Example source code is here

Upvotes: 10

Paulius Zaliaduonis
Paulius Zaliaduonis

Reputation: 5189

I have been using this on multiple free text search strings:

paramMap.$filter = paramMap.$filter.replace(/substringof\(('.+?'),(.+?)\)/, "contains($2,$1)");

It allows users to filter on multiple contains expressions. Example:

(Id gt 2 and (substringof('val1',FieldName) or substringof('val2',FieldName)))
// Translates to
(Id gt 2 and (contains(FieldName,'val1') or contains(FieldName,'val2')))

and

Upvotes: 0

XaviGuardia
XaviGuardia

Reputation: 186

The version provided by @Gongdo Gong is not perfect, if the expression ends in double parentheses, fails.

Example:

"(Id gt 2 and substringof('desde',Nombre))".replace(/substringof\((.+),(.+)\.)/, "contains($2,$1)")

Returns:

(Id gt 2 and contains(Nombre),'desde'))

The expression needs to be changed:

paramMap.$filter = paramMap.$filter.replace(/substringof\((.+),(.*?)\)/, "contains($2,$1)");

Upvotes: 4

Gongdo Gong
Gongdo Gong

Reputation: 1028

Here's a generalized version of Ryan Mrachek's answer by using regex replace.

parameterMap: function (data) {
    var d = kendo.data.transports.odata.parameterMap(data);
    if (d.$inlinecount) {
        if (d.$inlinecount == "allpages") {
            d.$count = true;
        }
        delete d.$inlinecount;
    }
    if (d.$filter) {
        d.$filter = d.$filter.replace(/substringof\((.+),(.+)\)/, "contains($2,$1)");
    }
    return d;
}

Upvotes: 4

Ryan Mrachek
Ryan Mrachek

Reputation: 771

Went with the simpler JS solution where the parameterMap is intercepted and changed to accommodate the new ODATA v4 function. 

var ds = new kendo.data.DataSource({
        type: 'odata',
        serverFiltering: true,
        serverPaging: true,
        pageSize: 10,
        transport: {
            read: {
                url: function () { return '{0}{1}'.format(_appRoot,_serviceUrl); },
                dataType: "json"
            }
            , parameterMap: function (data) {
                var d = kendo.data.transports.odata.parameterMap(data);
                delete d.$inlinecount;
                d.$count = true;

                if (d.$filter) {
                    // substringof('xYz',WorkUnitCode)  needs to 
                    // change to contains(WorkUnitCode,'06')
                    if (d.$filter.substring(0, 12) == 'substringof(') {
                        var parms = d.$filter.substring(12, d.$filter.length - 1).split(',');
                        d.$filter = 'contains({0},{1})'.format(parms[1],parms[0]);
                    }
                }

                return d;
            }
        },
        schema: {
            data: function (data) { return data.value; },
            total: function (data) { return data['@odata.count']; },
            model: _schemaModel
        }
        });

Upvotes: 3

Related Questions