Server-side grid filtering/grouping using Web API controller

[Dmytro Kuzmin]

Hello, 

Is it possible to implement server-side grid filtering/grouping when I'm using Web API method as datasource?

There's an example in the documentation on how to implement server-side paging:

var queryString = HttpContext.Current.Request.QueryString;

int skip = Convert.ToInt32(queryString["$skip"]);

int take = Convert.ToInt32(queryString["$top"]);

var data = db.Orders.Skip(skip).Take(take).ToList();

I'm not sure how to convert the query filter condition to C# where condition. Also I'm not sure if this feature would be protected against SQL injection.

[Dmytro Kuzmin]

Also it would be great to clarify server-side sorting using web api.

Thanks :)

[Venkatesh Ayothi Raman]

Hi Dmytro, 

Thank you for contacting Syncfusion support. 

We have created a following sample for your requirement. In this sample we have used wep Api adaptor with ODataQueryOptions. We can perform server side operations such as filtering, paging , sorting and grouping by using ODataQueryOptions parameter. Please refer to the sample and code example, 

Sample:http://www.syncfusion.com/downloads/support/directtrac/general/7z/WebApiSample_Modified-436034891 

  

Code example: 

<Grid> 

 

@(Html.EJ().Grid<object>("Grid") 

              .Datasource(ds => ds.URL("/api/Orders").Adaptor(AdaptorType.WebApiAdaptor)) 

              .AllowPaging() 

              .AllowGrouping() 

              .AllowSorting() 

              .AllowFiltering() 

              .FilterSettings(f => f.FilterType(FilterType.Excel)) 

              .PageSettings(page => { page.PageSize(8); }) 

               

               . . .  

              .Columns(col => 

              { 

                    . . .    

              }) 

    ) 

 

<Web API controller> 

 

NorthwindDataContext db = new NorthwindDataContext(); 

        public PageResult<OrdersView> Get(ODataQueryOptions opts) 

        { 

            var emp = db.OrdersViews.Take(50).AsQueryable(); 

            var count = emp.Count(); 

 

            if (opts.OrderBy != null) 

                emp = opts.OrderBy.ApplyTo(emp);  //perform sort 

            if (opts.Filter != null) 

                emp = opts.Filter.ApplyTo(emp, new ODataQuerySettings()).Cast<OrdersView>();  //perform filter 

            if (opts.InlineCount != null) 

                count = emp.ToList().Count; 

            if (opts.Skip != null) 

                emp = opts.Skip.ApplyTo(emp, new ODataQuerySettings());  //perform skip 

            if (opts.Top != null) 

                emp = opts.Top.ApplyTo(emp, new ODataQuerySettings());  //perform take 

 

 

            return new PageResult<OrdersView>(emp, null, count); 

 

        } 

Regards, 

Venkatesh Ayothiraman. 

[Dmytro Kuzmin]

Hello, Venkatesh Ayothiraman

Thanks for your answer. 

I've tried to use your solution, but I still have sime issues:

• ODataQueryOptions does not contain a definition for 'InlineCount'

This happens if I use AdaptorType.WebApiAdaptor.

After that I've tried to change my adaptor type to ODataV4Adaptor.

my code looks so:

           data = opts.OrderBy == null

                ? data.OrderBy(x => x.Id)       

                : opts.OrderBy.ApplyTo(data); //perform sort

            if (opts.Filter != null)

                data = opts.Filter.ApplyTo(data, new ODataQuerySettings()).Cast<OrderBasicEntityDto>();  //perform filter  

            if (opts.Count != null)

                count = data.Count();

            if (opts.Skip != null)

                data = opts.Skip.ApplyTo(data, new ODataQuerySettings());  //perform skip 

            if (opts.Top != null)

                data = opts.Top.ApplyTo(data, new ODataQuerySettings());  //perform take 

            return new PageResult<OrderBasicEntityDto>(data, null, count);

and now I'm getting the error:

ej.web.all.min.js:10 Uncaught TypeError: Cannot read property 'count' of undefined

However, the query returns correct result. And the count property is there

[Dmytro Kuzmin]

Alright.

I made this partially work with the ODataV4Adaptor.

• The problem was in returning PageResult, which has property "Items". Meanwhile the ODataV4Adaptor is expecting the property "result". Fixed this by returning new anonymous object instead of PageResult.
• Filter somehow works, but the result count is not correct (displaying total count). There's no option to get the count after applying the filter. 
• Searching doesn't work

Why it doesn't work:

• The ODataV4Adaptor should generate the query http://localhost:61760/api/Orders/?$count=true&... instead of $inlinecount=allpages&.... So that i can get the correct count after applying a filter via Request.ODataProperties().TotalCount

What is the source of issues:

• ODataV4Adaptor does not accept the correct PageResult
  
• WebApiAdaptor/ODataV4Adaptor generates query with inlinecount. The inlinecount is somehow obsolete. It should generate a query with count=true.
  

[Dmytro Kuzmin]

How can I implement custom OData adaptor, which retrieves Items instead of Result, and generates "$count=true&" instead of  $inlinecount=allpages& ?

[Venkatesh Ayothi Raman]

Hi Dmytro, 

Thanks for the update. 

The Web API controller and OData controller are different and $inlinecoun is not supported in ODataV4. So, OData functionality does not support API controller. So, Odata could not have properties like inlineCount(count) or $metadata. 

We have created a following sample for your requirement. Please refer to the code example, 

Code example: 

<Grid> 

@(Html.EJ().Grid<object>("Grid") 

              .Datasource(ds => ds.URL("/odata/Orders").Adaptor(AdaptorType.ODataV4Adaptor)) 

              .AllowPaging() 

              .AllowGrouping() 

              .AllowSorting() 

              .AllowFiltering() 

              . . . 

 

) 

 

<Controller> 

public async Task<Order> Put(int key, Order order) //Edit operation in database 

        { 

             

           var entity =  await db.Orders.FindAsync(order.OrderID); 

           db.Entry(entity).CurrentValues.SetValues(order); 

            await db.SaveChangesAsync(); 

            return order; 

        } 

        //// POST odata/Orders 

        public async Task<Order> Post(Order order) //Add Operation in database 

        { 

             

            db.Orders.Add(order); 

            await db.SaveChangesAsync(); 

            return order; 

        } 

 

 

        //// DELETE odata/Orders(5) 

        public async Task<IHttpActionResult> Delete([FromODataUri] int key) 

        { 

             

            var od = await db.Orders.FindAsync(key); 

            if (od == null) 

            { 

                return NotFound(); 

            } 

 

            db.Orders.Remove(od); 

            await db.SaveChangesAsync(); 

            return StatusCode(HttpStatusCode.NoContent); 

        } 

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/7z/EJGrid-839895244 

Regards, 

Venkatesh Ayothiraman. 

[Dmytro Kuzmin]

Hello, 

thanks for your help, I appreciate it. Using OData controller resolved all my issues.

Have a nice day,

Dmytro

[Venkatesh Ayothi Raman]

Hi Dymtro, 

Thank you for your feedback. 

We are happy to hear that your requirement is achieved. 

Thanks, 

Venkatesh Ayothiraman.