We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Server-side grid filtering/grouping using Web API controller

Thread ID:

Created:

Updated:

Platform:

Replies:

124841 Jul 3,2016 04:40 PM Jul 11,2016 01:19 AM ASP.NET MVC 8
loading
Tags: Grid
Dmytro Kuzmin
Asked On July 3, 2016 04:40 PM

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
Replied On July 3, 2016 05:13 PM

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

Thanks :)

Venkatesh Ayothi Raman [Syncfusion]
Replied On July 4, 2016 02:06 AM

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, 

  

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
Replied On July 5, 2016 11:38 AM

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
Replied On July 5, 2016 03:07 PM

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
Replied On July 5, 2016 03:33 PM

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

Venkatesh Ayothi Raman [Syncfusion]
Replied On July 6, 2016 08:39 AM

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); 
        } 




Regards, 
Venkatesh Ayothiraman. 


Dmytro Kuzmin
Replied On July 9, 2016 05:24 PM

Hello, 

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

Have a nice day,
Dmytro

Venkatesh Ayothi Raman [Syncfusion]
Replied On July 11, 2016 01:19 AM

Hi Dymtro, 

Thank you for your feedback. 

We are happy to hear that your requirement is achieved. 

Thanks, 
Venkatesh Ayothiraman. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;