How to achieve searching in grid with WCF/OData service?

4 mins read

This KB showcase the example to achieve the searching operation in grid when WCF/Odata service is used.

Solution

In WCF/Odata service, we don’t have the default searching support. So, to achieve the searching in remote data use customAdaptor to modify the default search query string.

Step 1: Render the Grid control

HTML

<div id="FlatGrid"></div>

 
$(function () {
     $("#FlatGrid").ejGrid({
        dataSource: ej.DataManager({
                           url: "http://js.syncfusion.com/demos/ejservices//Wcf/Northwind.svc/Orders/",
                           crossDomain:true,                                   
                           }),
         allowPaging: true,
         toolbarSettings: { showToolbar: true, toolbarItems: ej.Grid.ToolBarItems.Search] },
         load: "load",
         columns: [
                     { field: "OrderID", isPrimaryKey: true, headerText: "Order ID", width: 90, textAlign: ej.TextAlign.Right },
                     { field: "CustomerID", headerText: "CustomerID", width: 90 },
                     { field: "EmployeeID", headerText: "Employee ID",  width: 90  },
                     { field: "ShipCity", headerText: "Ship City", width: 90 },
                ]
            });
        });

MVC

@(Html.EJ().Grid<object>("FlatGrid")
            .Datasource("http://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders")
            .AllowPaging()    /*Paging Enabled*/
           .ToolbarSettings(toolbar =>
             {
               toolbar.ShowToolbar().ToolbarItems(items =>
                {
                  items.AddTool(ToolBarItems.Search);
        });
    })
            .ClientSideEvents(eve => eve.Load ("load"))
       .Columns(col =>
          {
            col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width(90).Add();
            col.Field("CustomerID").HeaderText("Customer ID”).Width(90).Add();
            col.Field("EmployeeID").HeaderText("Employee ID”) .Width(90).Add();
            col.Field("ShipCity").HeaderText("Ship City”) .Width(90).Add();
 
        }))

ASP

<ej:Grid ID="FlatGrid" runat="server"  AllowPaging="True" >
<DataManagerURL="http://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders/"></DataManager>
            <ClientSideEvents Load="load " />
            <Columns>
                <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="True"  Width="90" />
                 <ej:Column Field="CustomerID" HeaderText="Customer ID"  Width="80" />
                 <ej:Column Field="EmployeeID" HeaderText="Employee ID"  Width="80"/>
                 <ej:Column Field="ShipCity" HeaderText="Ship City"  Width="80" />
            </Columns>
            <ToolbarSettings ShowToolbar="True" ToolbarItems="search"></ToolbarSettings>
        </ej:Grid>

.Net core

<ej-grid id="FlatGrid " allow-paging="true" >
    <e-toolbar-settings show-toolbar="true" toolbar-items='@new List<string> {“search”}' />
< e-datamanager url="http://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders"></ e-datamanager >
 
    <e-columns>
        <e-column field="OrderID" is-primary-key="true" header-text="Order ID" width="90"></e-column>
        <e-column field="CustomerID" header-text="Customer ID" width="80”></e-column>
        <e-column field="EmployeeID" header-text="Employee ID" width="80”></e-column>
        <e-column field="ShipCity" header-text="Ship City" width="80”></e-column>
    </e-columns>
</ej-grid>

Angular

<ej-grid #grid [allowPaging]="true"  [dataSource]="gridData"    [toolbarSettings]="toolbarItems" (load)="load($event)" >
    <e-columns>
       <e-column field="OrderID" [isPrimaryKey] = "true" headerText="Order ID"  width="85" ></e-column>
        <e-column field="CustomerID" headerText="Customer ID"   width="100"></e-column>
        <e-column field="EmployeeID" headerText="Employee ID " width="90"></e-column>        
        <e-column field="ShipCity" headerText="ShipCity" width="60"></e-column> 
    </e-columns>
</ej-grid>

export class GridComponent {
    public gridData: any;
    public editSettings;
    public toolbarItems;
   @ViewChild('grid') Grid: EJComponents<any, any>;    
      constructor() {
       this.editSettings = { allowEditing: true, allowAdding: true, allowDeleting: true};
       this.toolbarItems = { showToolbar : true, toolbarItems : ["search"]};
        this.gridData = [{
           …………………
        ];
     
        }
        load(e: any){ 
                       this.Grid.model.dataSource.adaptor = new customadaptor(this.Grid.model.columns);//extend the adaptor    
            }
 var customadaptor = function (data) {
                     var odataSearch = new ej.ODataAdaptor().extend({
                     onComplexPredicate: function (pred, requiresCast) {
                var res = this.base.onComplexPredicate.apply(this, [pred, requiresCast])
                res = res.split(" or ");
                res = res.filter(function (element) {
                    return element !== "(undefined)";
                });
                return res.join(" " + pred.condition + " ");
            },
            init: function () {
                this.column = data;
            },
            onPredicate: function (pred, query, requiresCast) {
                var returnValue = "", operator, guid, val = pred.value, type = typeof val, field = this._p(pred.field);
                if (type === "string") { // handling string query
                    var args = this._typeStringQuery(pred, requiresCast, val, field, guid);
                    val = args["val"]; field = args["field"]; guid = args["guid"];
                }
                operator = ej.data.odUniOperator[pred.operator];
                var column = this.column.filter(function (e) { return e.field == pred.field })[0];
                if (column.type == "number" || column.type == "boolean") {
                    returnValue += "(" + pred.field + " eq " + pred.value + ")"; // handling number and boolean query
                } else {
                    if (operator === "substringof") {
                        var t = val;
                        val = field;
                        field = t;
                    }
                    returnValue += operator + "(" + field + "," + val + ")";
                }
                if ((!$.isNumeric(pred.value) && column.type == "number") || column.type == "date" || column.type == "datetime") { 
                    return;
                } else
                    return returnValue;
            },
        });
        $.extend(this, new odataSearch());
        return this;
    }
  }
}

Step 2: Modify the search query in the customAdaptor function

 
var customadaptor = function (data) {
        var odataSearch = new ej.ODataAdaptor().extend({
            onComplexPredicate: function (pred, requiresCast) {
                var res = this.base.onComplexPredicate.apply(this, [pred, requiresCast])
                res = res.split(" or ");
                res = res.filter(function (element) {
                    return element !== "(undefined)";
                });
                return res.join(" " + pred.condition + " ");
            },
            init: function () {
                this.column = data;
            },
            onPredicate: function (pred, query, requiresCast) {
                var returnValue = "", operator, guid, val = pred.value, type = typeof val, field = this._p(pred.field);
                if (type === "string") { // handling string query
                    var args = this._typeStringQuery(pred, requiresCast, val, field, guid);
                    val = args["val"]; field = args["field"]; guid = args["guid"];
                }
                operator = ej.data.odUniOperator[pred.operator];
                var column = this.column.filter(function (e) { return e.field == pred.field })[0];
                if (column.type == "number" || column.type == "boolean") {
                    returnValue += "(" + pred.field + " eq " + pred.value + ")"; // handling number and boolean query
                } else {
                    if (operator === "substringof") {
                        var t = val;
                        val = field;
                        field = t;
                    }
                    returnValue += operator + "(" + field + "," + val + ")";
                }
                if ((!$.isNumeric(pred.value) && column.type == "number") || column.type == "date" || column.type == "datetime") { 
                    return;
                } else
                    return returnValue;
            },
        });
        $.extend(this, new odataSearch());
        return this;
    }
}

Step 3: Extend the OData adaptor as custom adaptor in grid load event.

</script>
  function load(args) {
 
        this.model.dataSource.adaptor = new customadaptor(this.model.columns);//extend the adaptor    
 
    } 
 
</script>

Refer the below link for JS-playground sample

https://jsplayground.syncfusion.com/jb1j4q2w

Also refer the below link for custom adaptor script file.

https://www.syncfusion.com/downloads/support/directtrac/general/ze/customadaptor-1287990129.zip

Note:

Please refer the custom Adaptor script file after the ej.web.all.min.js file.

Query string