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. Image for the cookie policy date

Server-side grid filtering/grouping using Web API controller

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.


8 Replies

DK Dmytro Kuzmin July 3, 2016 09:13 PM UTC

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

Thanks :)


VA Venkatesh Ayothi Raman Syncfusion Team July 4, 2016 06:06 AM UTC

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. 



DK Dmytro Kuzmin July 5, 2016 03:38 PM UTC

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





DK Dmytro Kuzmin July 5, 2016 07:07 PM UTC

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.



DK Dmytro Kuzmin July 5, 2016 07:33 PM UTC

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


VA Venkatesh Ayothi Raman Syncfusion Team July 6, 2016 12:39 PM UTC

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. 



DK Dmytro Kuzmin July 9, 2016 09:24 PM UTC

Hello, 

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

Have a nice day,
Dmytro


VA Venkatesh Ayothi Raman Syncfusion Team July 11, 2016 05:19 AM UTC

Hi Dymtro, 

Thank you for your feedback. 

We are happy to hear that your requirement is achieved. 

Thanks, 
Venkatesh Ayothiraman. 


Loader.
Up arrow icon