This is a migrated thread and some comments may be shown as answers.

Web API 2.2 service that provides server side filtering, paging and sorting

3 Answers 278 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Scott asked on 05 Jul 2016, 10:09 PM

I've been fighting to figure out what combination of Web API REST services and the Kendo UI Grid work well with both Microsoft Web API 2.2 (latest version for .NET 4.6) and the Kendo UI Grid version 2016.2.504, commercial.

OData v4 is very easy to develop on the backend (Web API) but server side filtering/sorting/paging seems to be broken on the front end (Kendo UI). Despite setting the transport type to “odata-v4” the GET request that is being sent to the service doesn’t even contain the dollar signs before the parameter keys such as $filter or $top much less being actual OData v4 formats.

Ex: /odata/vwApplication?take=10&skip=0&page=1&pageSize=10&filter%5Bfilters%5D%5B0%5D%5Bfield%5D=FundingPeriodId&filter%5Bfilters%5D%5B0%5D%5Boperator%5D=eq&filter%5Bfilters%5D%5B0%5D%5Bvalue%5D=1&filter%5Bfilters%5D%5B1%5D%5Bfield%5D=StudentId&filter%5Bfilters%5D%5B1%5D%5Boperator%5D=eq&filter%5Bfilters%5D%5B1%5D%5Bvalue%5D=3369&filter%5Blogic%5D=and HTTP/1.1

Using OData is not a requirement, it just seemed the simplest way to bind the Kendo UI Grid and get server side filtering, sorting and paging. I’m open to anything that’s quick to develop and easy to maintain (otherwise I’d just write it myself and not buy these kinds of tools).

Note: I am not interested in using any form of ASP on the front end, including MVC. Pure REST services only.

I'm down to having to write a recursive function in JavaScript to parse the Grid's filter to convert it into a proper OData v4 filter which seems rather silly considering we bought the commercial version to make setting up a grid quick and easy.

Is there an example that shows how to write a modern Web API 2 service in C# and bind it to the Kendo UI Grid in JavaScript that provides server side filtering, sorting and paging? I find projects for MVC that are several years old but not a newer pure Web API REST example.

You would think all of this should be as easy as telling the Kendo UI Grid to use OData v4 but after two days of reading and tweaking I don’t seem to be getting anywhere.

Thank you ahead of time for your help!

3 Answers, 1 is accepted

answered on 07 Jul 2016, 02:11 PM

Thank you for your help Boyan, but the closest example I could find requires MVC on the front end from what I can see. Maybe I'm looking at the wrong example?

I'm use Kendo UI with 100% JavaScript / HTML5 / CSS only for my client side code. I'm using zero server side technology and have no desire to do so.

I'm not tied to OData, in fact I don't even like it. If there's a .NET library that will take your standard for sorting, filtering and paging and apply it to Entity Framework to provide server side support in my Web API code I'd be happy with that. If there's no good solution for this that's what I plan to do, though I'd rather have a solution from you guys, if possible.

When I set the grid datasource transport to json I get the following when I set a filter to get records where StudentFirstName starts with R and ends with l...

http://localhost:51521/odata/vwApplication?take=10&skip=0&page=1&pageSize=10&filter[filters][0][field]=StudentFirstName&filter[filters][0][operator]=startswith&filter[filters][0][value]=R&filter[filters][1][field]=StudentFirstName&filter[filters][1][operator]=endswith&filter[filters][1][value]=l&filter[logic]=and

...which is the equivalent to this with LINQ in Entity Framework 6...

var results = db.Applications.Where(w => w.StudentFirstName.Trim().ToUpper().StartsWith("R") && w.StudentFirstName.Trim().ToUpper().EndsWith("L")).Skip(0).Take(10);

..the hard part is going to be translating this...

filter[filters][0][field]=StudentFirstName&filter[filters][0][operator]=startswith&filter[filters][0][value]=R&filter[filters][1][field]=StudentFirstName&filter[filters][1][operator]=endswith&filter[filters][1][value]=l&filter[logic]=and

...to this...

Where(w => w.StudentFirstName.Trim().ToUpper().StartsWith("R") && w.StudentFirstName.Trim().ToUpper().EndsWith("L"))

...or something equivalent.

Thanks again!

answered on 08 Jul 2016, 01:47 PM

I found my issue and it's so simple I'm embarrassed to admit it, but maybe this will help someone else who did the same thing I did.

I had set the type in the transport object, not the dataSource object.

Incorrect
dataSource: {
                transport: {
                                type: "odata-v4",
                                read: {
                                                url: appServiceBaseURL + "odata/vwStudent"
                                }
                }
}

Correct
dataSource: {
                type: "odata-v4",
                transport: {
                                read: {
                                                url: appServiceBaseURL + "odata/vwStudent"
                                }
                }
}

I knew it had to be something simple! Everything seems to be working as expected now.

It would be nice if you had an example of using a pure JavasScript front end with a pure Web API 2.2 backend though. I might have caught my error sooner.

Thanks for your help Boyan.