Working MVC 4 WebAPI Paging Example

17 posts, 0 answers
  1. Gary
    Gary avatar
    28 posts
    Member since:
    Apr 2011

    Posted 12 Apr 2012 Link to this post

    I have been playing around with the WebAPI and have got the paging working. This is a workaround until the day comes when WebAPI and Kendo can work together properly.

    The principle is to create a method that has the parameters take, skip, page and pageSize.

    Example, in my "Transactions" Controller I have a WebAPI method:
        Public Function GetTransactions(take As Integer, skip As Integer, page As Integer, pageSize As Integer
    As
     IQueryable(Of Transaction)        
    Return
     repository.GetPaged(take, skip, page, pageSize)     End Function
    This will then take the parameters passed by the URL generated from the kendo data source.

    Example (from fiddler)
          /[your web api path]/api/Transactions?take=10&skip=0&page=1&pageSize=10

    Before this will work, it is important for paging that the kendo data source knows the total number of rows that the query could return if it were not constrained by the take parameter. Not knowing how to add this to the results as a separate field, I added this to each row returned (added a property to the Transaction class). A bit of a waste, but it works.

    It required a small function in the schema section of the data source that returns total value from the first row of the data set.

    So, to the Kendo code. This uses a shared data source and a grid + chart. Of course, you'll need to change the data/uri to suit your WebAPI data.

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <!--In the header of your page, paste the following for Kendo UI Web styles-->
        <link href="scripts/kendo/styles/kendo.common.min.css" rel="stylesheet" type="text/css" />
        <link href="scripts/kendo/styles/kendo.default.min.css" rel="stylesheet" type="text/css" />
     
        <!--Then paste the following for Kendo UI Web scripts-->
        <script src="scripts/kendo/js/jquery.min.js"></script>
        <script src="scripts/kendo/js/kendo.all.min.js"></script>
        <title></title>
    </head>
    <body>
        <div id="example" class="k-content">
                <div id="grid"></div>
                <div id="chart"></div>
     
                <script type="text/javascript">
                    var sharedData;
     
                    function createChart() {
                        $("#chart").kendoChart({
                            dataSource: sharedData,
                            autoBind: false,
                            legend: {
                                visible: false
                            },
                            series: [{
                                type: "column",
                                field: "Amount"
                            }],
                            axisDefaults: {
                                labels: {
                                    font: "11px Tahoma, sans-serif"
                                }
                            },
                            valueAxis: {
                                labels: {
                                    format: "{0:N0}"
                                }
                            },
                            categoryAxis: {
                                field: "TransactionCreated"
                            },
                            tooltip: {
                                visible: true,
                                format: "{0:N0}"
                            }
                        });
                    }
     
                    function createGrid() {
                        $("#grid").kendoGrid({
                            dataSource: sharedData,
                            autoBind: false,
                            height: 250,
                            filterable: true,
                            sortable: true,
                            pageable: true,
                            columns: [{
                                field: "Id",
                                filterable: false
                            },
                            {
                                field: "TransactionCreated",
                                title: "Date Processed",
                                width: 100,
                                format: "{0:MM/dd/yyyy HH:MM}",
                                filterable: false
                            },
                            {
                                field: "TransactionCompleted",
                                title: "Date Complete",
                                width: 100,
                                format: "{0:MM/dd/yyyy HH:MM}",
                                filterable: false
                            },
                            {
                                field: "CardType",
                                title: "Card Type"
                            },
                            {
                                field: "Settled",
                                title: "Settled"
                            },
                            {
                                field: "Amount",
                                title: "Amount",
                                filterable: false
                            },
                            {
                                field: "Currency",
                                title: "Currency"
                            },
                            {
                                field: "OrderReference",
                                title: "Order Reference",
                                filterable: false
                            },
                            {
                                field: "MaskedCardNumber",
                                title: "Card Number",
                                filterable: false
                            },
                            {
                                field: "ReferenceNumber",
                                title: "Reference Number",
                                filterable: false
                            },
                            {
                                field: "ResponseCode",
                                title: "Response Code"
                            }
                        ]
                        });
                    }
     
                    $(document).ready(function () {
                        sharedData = new kendo.data.DataSource({
                            transport: {
                                read: "http://localhost/WebAPITest/api/Transactions",
                                datatype: "json"
                            },
                            schema: {
                                total: function (result) {
                                    var cnt = 0;
                                    if (result.length > 0)
                                        cnt = result[0].Count;
                                    return cnt;
                                },
                                model: {
                                    fields: {
                                        Id: { type: "number" },
                                        TransactionCreated: { type: "date" },
                                        TransactionCompleted: { type: "date" },
                                        CardType: { type: "string" },
                                        Settled: { type: "string" },
                                        Amount: { type: "number" },
                                        Currency: { type: "string" },
                                        OrderReference: { type: "string" },
                                        MaskedCardNumber: { type: "string" },
                                        ReferenceNumber: { type: "number" },
                                        ResponseCode: { type: "string" },
                                        Count: { type: "number" }
                                    }
                                }
                            },
                            page: 1,
                            pageSize: 10,
                            serverPaging: true,
                            serverFiltering: true,
                            serverSorting: true
                        });
     
                        createGrid();
                        createChart();
     
                        sharedData.fetch();
     
     
                    });
                </script>
        </div>
    </body>
    </html>
    

    Hope this helps.
  2. Long
    Long avatar
    1 posts
    Member since:
    Feb 2012

    Posted 14 Apr 2012 Link to this post

    Here's a full working example with serverside paging, sorting and dynamic filteration which works with MVC4:
    http://blog.longle.net/2012/04/13/teleriks-html5-kendo-ui-grid-with-server-side-paging-sorting-filtering-with-mvc3-ef4-dynamic-linq/
  3. Kendo UI is VS 2017 Ready
  4. Gary
    Gary avatar
    28 posts
    Member since:
    Apr 2011

    Posted 16 Apr 2012 Link to this post

    Yes, that's a good example but not relevent to the new ASP.NET MVC 4, WebAPI.

    The new WebAPI will become the defacto way of building a .NET based API (IMHO) for your applications (mobile, web etc.)
    and for exposing a public API to your customers who want to build their own applications against the data
    you hold.

    WebAPI is an evolution of WCF REST and uses some of the paradigms in ASP.NET MVC but does not
    have to be used as part of an MVC site. It can be used independently. This is why I am interested in this
    technology. Our front end will not be MVC, it will probably be HTML/JQuery/CSS3 based (probably Kendo).

  5. Georgi Tunev
    Admin
    Georgi Tunev avatar
    7207 posts

    Posted 17 Apr 2012 Link to this post

    Hello Gary,

     Thank you for sharing your approach with the community. I'd like to invite you to share a full sample in our Code Library section - we would be happy to add some Telerik points to your account in return :)

    Regards,
    Georgi Tunev
    the Telerik team
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
  6. James
    James avatar
    6 posts
    Member since:
    Mar 2012

    Posted 17 Apr 2012 Link to this post

    This looks like a great start!

    However a couple of questions...

    How does the source know how to use your parameters? I can't see anything in the code that is doing so. I'm probably just blind.

    Obviously this isn't really odata and thus you cannot use Web API's automatic oData syntax support...

    Is there not a way to tell it that it's an odata source, AND have Kendo handle the json response back as not an odata data type? (i.e. json datatype instead of odata data type?)

    This is one of 2 major roadblocks I'm having. The second is $select support not being there with web api... Any assistance to get this to work in a semi automatic way would be fantastic.
  7. Gary
    Gary avatar
    28 posts
    Member since:
    Apr 2011

    Posted 17 Apr 2012 Link to this post

    James,

    The WebAPI maps URL parameters directly to method parameters by name (I think) so as long as you match URL parameters being passed by Kendo to the method, it will work transparently.

    So:
    GetTransactions(take, skip, page, pageSize)

    Will automatically get the values passed from Kendo in the URL
    https://yourWebAPI-App/api/Transactions?take=10&skip=0&page=1&pageSize=10

    I have not experimented with the order, so this may be important but I suspect they just matched by name.

    I started off trying to use the "type: 'odata'" attribute on the data source but I think there's a bug with this. It did not use the odata syntax of passing the variable names prefixed with a "$". Saying that, I think there's more to it as I tried to emulate a correct URL syntax in Fiddler and that didn't work either.

    However, WebAPI is a Beta release so there may be bugs in that too.

    As for $select support, you could use $filter perhaps or pass in the $select as a parameter and decode it or try and pass it dynamically to an EF model. Not sure if that's possible.

    What we both (I am working on a similar project to you probably) is that WebAPI is not ODATA. WebAPI is REST/JSON (or XML) and this is not quite the same. There is no standard for REST but there is for OData.

    I'll post again once I've done more research.

     
  8. James
    James avatar
    6 posts
    Member since:
    Mar 2012

    Posted 17 Apr 2012 Link to this post

    Thank Gary.

    I was more meaning, how does Kendo go and set those automatically with the paging? (i.e. Take, skip, etc.) I didn't see anything in the Datasource and I'm just getting used to it too. (I would love more documentation on the Datasource functionality and how it works and what it's doing. It looks like it's largely a wrapper around $.ajax but...)

    Here's what I've found for Web API:

    It fully supports the following:

    $filter
    $orderby
    $skip
    $take
    $top

    I haven't been able to find any exceptions to this.

    However it's problem with Kendo and anything else using OData, is that it returns simple json, not OData Json (or OData Atom/xml).

    I would have expected that specifying a dataType: "json" to the Kendo Datasource that it would have been able to handle this, however it blows up trying to parse the response with an invalid token. (i.e. it's looking for the oData syntax)

    So since I've already replaced the standard json formatter with a json.net version, what I'm doing now is looking at the value of the OnReadFromStreamAsync and if it's IQueryable, I am instead of just pure json, going to encode oData if I can... at least a basic wrapper that will work...

    Would love it if Kendo people would save me this effort and release an interim build that would handle pure json as a response from odata instead but would recognize the count as a property etc.
  9. Gary
    Gary avatar
    28 posts
    Member since:
    Apr 2011

    Posted 17 Apr 2012 Link to this post

    James,

    In my example, the binding to the Grid enables a trigger of the data source fetch() when you select another page.
    The values for Take and Skip are calculated, by the DataSource I think once the Page is set by the Grid on the
    DataSource.

    As the Chart is bound go the same data source, it gets updated too. It's all under the hood of DataSource. I
    suspect you could do this manually by setting the page and calling fetch() if you wanted to implement your
    own paging control.

    Gary
  10. James
    James avatar
    6 posts
    Member since:
    Mar 2012

    Posted 18 Apr 2012 Link to this post

    Sorry but I just don't see HOW the data source even knows to use take, skip etc. instead of $take, $skip. I could understand that it would be able to automagically send $take and $skip if it knew it was an odata source. But as soon as you changed your parameters I don't see how it's possible.

    I would really like Telerik to do a MUCH better job describing exactly how the data source works, what the options are and how to implement custom functionality in it.

    I'd say that making it work with Web API would be an excellent excuse to do so as a blog or something that shows us the magic behind the datasource.

    As it is right now, it scares me because when it breaks, there is essentially no way to know why it broke or to do anything about it. It either works or you're screwed.
  11. Gary
    Gary avatar
    28 posts
    Member since:
    Apr 2011

    Posted 18 Apr 2012 Link to this post


    James,

    That was the original issue and why paging did not work with WebAPI. When I set "odata" as the type and
    looked in fiddler, it was passing the parameters in the query string, but NOT with $ prefixes, so there is no
    way this would work.

    My solution is a workaround for this, as you should not need to map parameters when returning an IQueryable
    from a WebAPI method. It should get mapped as appended fluent calls to Skip Top etc, which it obviously was not.

    Actually it's just occurred to me that the reason this did not work is that it would not have understood
    some of the parameter names. I Think for Paging the WebAPI uses Top and Skip only, not Take, Page or PageSize.
    So, Kendo does need to do this translation, which it is not.

    Gary
  12. James
    James avatar
    6 posts
    Member since:
    Mar 2012

    Posted 18 Apr 2012 Link to this post

    That's not at all what I'm seeing.

    Kendo when in OData mode is passing $filter, etc. just fine, and putting a break point in my custom json formatter is showing that it is indeed filtering.

    However if I use your example it's passing bizzare filtering commands that are not odata and I don't have a clue where it's deciding to use them from. (which is the default when you tell it the type is "json")

    You would then have to match all of the parameters per method that you setup exactly with what Kendo is passing and manually filter these.

    Obviously that is incredibly non-optimal and when MS fixes this stuff and responds with true oData back, all of your methods are going to need to be written.

    I've attached a file for what I've done so far with this.

    This should be registered as a mediatypeformatter. It uses json.net to encode the json response properly instead of the busted version that is in the .net framework. (There are lots of samples online on how to do this in your global.asax)

    It's not quite right yet, but it's getting close to being proper odata syntax.

    you'll note that the TotalCount is passed to the mediatypeformatter, but it's always -1 right now. Apparently this is being fixed before final release, so at least in theory we can get the TotalCount.

    From the mvc.net source code I can confirm that currently the following are supported:

    $filter
    $orderby
    $skip
    $top

    $take is not supported and you're correct that the others aren't supported either. (and won't be for final version according to ms)

    I have created an issue on code plex: http://aspnetwebstack.codeplex.com/workitem/66 

    Basically asking, since they refuse to add the rest of the odata functions to make the entire odata stack public instead of internal. If they do this (they say that the will) then it will be easy to ineherit the QueryableAttribute and add our own.

    Hence I need to be able to get into the guts of the Kendo or have Telerik give us the simple ability to do two things:

    1. Allow odata to return a simple json response and not blow up. (or have them tell me why my odata response isn't valid so that I can fix it in which case it doesn't need to return json)
    2. Have a param that says to just use top and skip instead of take.

    If we can get these two minor work arounds in place, then we're golden and don't have to do crazy stuff that we're going to have to undo later en-mass because we can turn off the param that uses top instead of take, and for select it's fine to pass it, even if it doesn't work for now, and we can add it in the backend in one spot and it will work for all of them going forward.

    So how about it Telerik? How about a minor update that handles these two cases? I'd be happy to test.

    Once I get this working I'll be purchasing. But until I do, I can't spend the money on non-working product.

    (Just FYI, the alternative is likely to use upshot.js and bind the kendo data source to it, but i haven't tried yet)






  13. James
    James avatar
    6 posts
    Member since:
    Mar 2012

    Posted 19 Apr 2012 Link to this post

    I've finally got a good solution for Kendo working with Web API methods that return IQueryable and accept OData commands:

    Here's a sample of an AutoComplete:

    $("#txtSearch").kendoAutoComplete({
        minLength: 3,
        dataTextField: "Name", // JSON property name to use
        dataSource: new kendo.data.DataSource({
            type: "json", // specifies data protocol
            pageSize: 10, // limits result set
            serverPaging: true,
            serverSorting: true,
            serverFiltering: true,
            transport: {
                read: "/api/contacts/LookupContactInfo"
            },
            error: function (e) {
                debugger;
            },
            change: function () {
                debugger;
            },
        })
    })

    Note that it enables serverPaging, and Filtering and sorting and that the type is just json.

    Now, the trick is to translate the standard json filtering etc. to odata on the server side before it gets passed to the method.


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net.Http;
    using System.Net;
    using System.Web.Http;
    using System.Web.Http.Data.EntityFramework;
    using System.Web.Security;
    using System.Web;
     
     
    namespace JSON {
     
        [JsonToODataActionFilter]
        public abstract class NewDataController<T> : DbDataController<T> where T : System.Data.Entity.DbContext, new() {
        }
     
     
        public class JsonToODataActionFilter : System.Web.Http.Filters.ActionFilterAttribute {
            public override void OnActionExecuting(System.Web.Http.Controllers.HttpActionContext actionContext) {
                var Uri = actionContext.Request.RequestUri;
                System.Collections.Specialized.NameValueCollection QueryItems = actionContext.Request.RequestUri.ParseQueryString();
                int Skip = 0;
                for (int j = 0; j < QueryItems.AllKeys.Length; j++) {
                    switch (QueryItems.Keys[j].ToLower()) {
                        case "take":                       
                            QueryItems.Add("$top", QueryItems[j]);
                            break;
                        case "skip":
                            Skip = QueryItems[j].ToInt32();
                            QueryItems.Add("$skip", QueryItems[j]);
                            break;
                        default:
                            break;
                    }
                }
     
     
     
     
                QueryItems.Remove("take");
                QueryItems.Remove("skip");
     
                ApplyFilter(ref QueryItems);
     
                var NewUri = new UriBuilder(Uri);
                NewUri.Query = QueryItems.ToString();
                 
                actionContext.Request.RequestUri = NewUri.Uri;
            }
     
            private void ApplyFilter(ref System.Collections.Specialized.NameValueCollection QueryItems) {
                if (string.IsNullOrWhiteSpace(QueryItems["filter[logic]"]))
                    return;
     
                string Logic = QueryItems["filter[logic]"];
                QueryItems.Remove("filter[logic]");
     
                int j = 0;
                List<GridFilter> Filters = new List<GridFilter>();
                while (!string.IsNullOrWhiteSpace(QueryItems["filter[filters][" + j + "][field]"])) {
                    var item = new GridFilter() { Field = QueryItems["filter[filters][" + j + "][field]"], Operator = QueryItems["filter[filters][" + j + "][operator]"], Value = QueryItems["filter[filters][" + j + "][value]"] };
                    Filters.Add(item);
                    QueryItems.Remove("filter[filters][" + j + "][field]");
                    QueryItems.Remove("filter[filters][" + j + "][operator]");
                    QueryItems.Remove("filter[filters][" + j + "][value]");
                    j++;
                }
     
                if (Filters.Count == 0)
                    return;
     
                //Now convert this to odata syntax
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                foreach (var item in Filters) {
                    string oFilter = null;
                    switch (item.Operator.ToLower()) {
                        case "startswith":
                            oFilter = string.Format("startswith({0}, '{1}') eq true", item.Field, item.Value);
                            break;
                        case "endswith":
                            oFilter = string.Format("endswith({0}, '{1}') eq true", item.Field, item.Value);
                            break;
                        case "contains":
                            oFilter = string.Format("indexof({0}, '{1}') Gt -1", item.Field, item.Value);
                            break;
                        case "eq":
                            oFilter = string.Format("{0} Eq '{1}'", item.Field, item.Value);
                            break;
                        case "neq":
                        case "ne":
                            oFilter = string.Format("{0} Ne '{1}'", item.Field, item.Value);
                            break;
                        case "gt":
                            oFilter = string.Format("{0} Gt {1}", item.Field, item.Value);
                            break;
                        case "gte":
                            oFilter = string.Format("{0} Ge {1}", item.Field, item.Value);
                            break;
                        case "lt":
                            oFilter = string.Format("{0} Lt {1}", item.Field, item.Value);
                            break;
                        case "lte":
                            oFilter = string.Format("{0} Le {1}", item.Field, item.Value);
                            break;
     
                    }
     
                    if (oFilter == null)
                        continue;
     
                    if (sb.Length == 0) {
                        sb.Append(oFilter);
                    } else {
                        sb.AppendFormat(" {0} {1}", Logic, oFilter);
                    }
     
                }
     
                if (sb.Length == 0)
                    return;
     
                QueryItems.Add("$filter", sb.ToString());
            }
     
            public override void OnActionExecuted(System.Web.Http.Filters.HttpActionExecutedContext actionExecutedContext) {
                base.OnActionExecuted(actionExecutedContext);
            }
        }
     
        public class GridFilter {
            public string Operator { get; set; }
            public string Field { get; set; }
            public string Value { get; set; }
        }

    What this does is put an action filter on all DbDataControllers. Simply implemente NewDataController instead of DbDataController and it will automatically start working.

    What this does is convert take/skip to $top and $skip

    And then go and convert the filter format to $filter for odata.

    End result is that the standard filtering and paging that the data controller will do with json works perfectly, which allows kendo to recognize the response back unlike telling kendo to use odata because Web API doesn't respond with odata back.

    Obviously this isn't yet complete because it doesn't handle $orderby yet. I'll be adding that when I get to it.

    I'm also adding $select, and $inlinecount support to Web API through the same method except on the OnActionExecuted  (hopefully it works!)

    Hopefully this helps someone. The beauty of doing it this way is that if Kendo comes out and supports Web API's implementation of OData in the future, just disable the translation and change the type on the jquery for the datasource to whatever they specify. If MS starts writting back JSON oData responses in the future, just set the datasource type to odata and let it do it's thing by removing the attribute.

    Either way you win without having to resort to ugly work arounds.
  14. Gary
    Gary avatar
    28 posts
    Member since:
    Apr 2011

    Posted 19 Apr 2012 Link to this post

    James,
    Thanks for that. Haven't had time to work on this since last weekend. Will fit your changes in soon and see what happens.

    I'd post your solution on the Code Examples forum and get some Telerik points!

    Hopefully these two great bits of tech will work together seemlessly very soon.

    Gary
  15. James
    James avatar
    6 posts
    Member since:
    Mar 2012

    Posted 19 Apr 2012 Link to this post

    for $select to work you need to add System.Linq.Dynamic to your ActionFilterAttribute usings. (go online to find it)

    Then change the OnActionExecuted to:

    public override void OnActionExecuted(System.Web.Http.Filters.HttpActionExecutedContext actionExecutedContext) {
        HttpRequestMessage request = actionExecutedContext.Request;
        HttpResponseMessage response = actionExecutedContext.Result;
     
        IQueryable query;
        if (response != null && response.TryGetObjectValue(out query)) {
            bool Changed = false;
     
            System.Collections.Specialized.NameValueCollection QueryItems = request.RequestUri.ParseQueryString();
            string select = QueryItems["$select"];
            if (!string.IsNullOrWhiteSpace(select)) {
                query = query.Select(string.Format("new ({0})", select));
                Changed = true;
            }
     
            if (Changed) {
                var enumerator = query.GetEnumerator();
     
                List<dynamic> l = new List<dynamic>();
                while (enumerator.MoveNext())
                    l.Add(enumerator.Current);
     
                actionExecutedContext.Result = new HttpResponseMessage(HttpStatusCode.OK);
                actionExecutedContext.Result.CreateContent(l);
            }
        }
    }


    Volia, now you can do projections.

    Only thing left is mapping the order by stuff, which I'll do as soon as I need it...
  16. Mike
    Mike avatar
    7 posts
    Member since:
    Sep 2009

    Posted 15 Jun 2012 Link to this post

    James,

    First of all, THANK YOU for posting the code above. I followed in your footsteps and made some modifications to allow the orderby. Also, I updated it to be compatible for WebAPI RC. There were a few changes that needed to be made. It looks as though the operators in the filters are case sensitive (for example "Eq" was not working, while "eq" does). Also, the actionExecutedContext.Result is now .Response and the CreateContent is an extension method off of actionExecutedContext.Request.

    Here is some updated code below with orderby and the other WebAPI RC updates:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net.Http;
    using System.Net;
    using System.Web.Http;
    using System.Web.Security;
    using System.Web;
    using System.Linq.Dynamic;
     
    namespace JSON
    {
         
              
        public class JsonToODataActionFilter : System.Web.Http.Filters.ActionFilterAttribute
        {
            public override void OnActionExecuting(System.Web.Http.Controllers.HttpActionContext actionContext)
            {
                var Uri = actionContext.Request.RequestUri;
                System.Collections.Specialized.NameValueCollection QueryItems=actionContext.Request.RequestUri.ParseQueryString();
     
                ApplyPaging(ref QueryItems);
                ApplyFilter(ref QueryItems);
                ApplyOrderBy(ref QueryItems);
      
                var newUri = new UriBuilder(Uri);
                newUri.Query=QueryItems.ToString();
                  
                actionContext.Request.RequestUri = newUri.Uri;
            }
     
            private void ApplyPaging(ref System.Collections.Specialized.NameValueCollection QueryItems)
            {
                int Skip = 0;
                for (int j = 0; j < QueryItems.AllKeys.Length; j++)
                {
                    switch (QueryItems.Keys[j].ToLower())
                    {
                        case "take":
                            QueryItems.Add("$top", QueryItems[j]);
                            break;
                        case "skip":
                            Skip = Convert.ToInt32(QueryItems[j]);
                            QueryItems.Add("$skip", QueryItems[j]);
                            break;
                        default:
                            break;
                    }
                }
     
                QueryItems.Remove("take");
                QueryItems.Remove("skip");
     
            }
     
            private void ApplyOrderBy(ref System.Collections.Specialized.NameValueCollection QueryItems)
            {
     
                int j = 0;
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                while (!string.IsNullOrWhiteSpace(QueryItems["sort[" + j + "][field]"]))
                {
                    if (!string.IsNullOrWhiteSpace(QueryItems["sort[" + j + "][dir]"]))
                    {
                        sb.AppendFormat("{0} {1},", QueryItems["sort[" + j + "][field]"], QueryItems["sort[" + j + "][dir]"]);
                    }
                    else
                    {
                        sb.AppendFormat("{0} {1},", QueryItems["sort[" + j + "][field]"], "asc");
                    }
                    QueryItems.Remove("sort[" + j + "][field]");
                    QueryItems.Remove("sort[" + j + "][dir]");
                     
                    j++;
                }
     
                if (sb.Length == 0)
                {
                    return;
                }
                else
                {
                    sb.Remove(sb.Length - 1, 1);
                    QueryItems.Add("$orderby", sb.ToString());
                }
     
            }
      
            private void ApplyFilter(ref System.Collections.Specialized.NameValueCollection QueryItems)
            {
                if (string.IsNullOrWhiteSpace(QueryItems["filter[logic]"]))
                    return;
      
                string logic = QueryItems["filter[logic]"];
                QueryItems.Remove("filter[logic]");
      
                int j=0;
                List<GridFilter> filters = new List<GridFilter>();
                while (!string.IsNullOrWhiteSpace(QueryItems["filter[filters][" + j + "][field]"]))
                {
                    var item=new GridFilter()
                    {
                        Field=QueryItems["filter[filters][" + j + "][field]"],
                        Operator=QueryItems["filter[filters][" + j + "][operator]"],
                        Value=QueryItems["filter[filters][" + j + "][value]"]
                    };
                    filters.Add(item);
                    QueryItems.Remove("filter[filters][" + j + "][field]");
                    QueryItems.Remove("filter[filters][" + j + "][operator]");
                    QueryItems.Remove("filter[filters][" + j + "][value]");
                    j++;
                }
      
                if (filters.Count == 0)
                    return;
      
                //Now convert this to odata syntax
                System.Text.StringBuilder sb=new System.Text.StringBuilder();
                foreach (var item in filters)
                {
                    string oFilter=null;
                    switch (item.Operator.ToLower())
                    {
                        case "startswith":
                            oFilter=string.Format("startswith({0}, '{1}') eq true", item.Field, item.Value);
                            break;
                        case "endswith":
                            oFilter=string.Format("endswith({0}, '{1}') eq true", item.Field, item.Value);
                            break;
                        case "contains":
                            oFilter=string.Format("indexof({0}, '{1}') gt -1", item.Field, item.Value);
                            break;
                        case "eq":
                            oFilter=string.Format("{0} eq '{1}'", item.Field, item.Value);
                            break;
                        case "neq":
                        case "ne":
                            oFilter=string.Format("{0} ne '{1}'", item.Field, item.Value);
                            break;
                        case "gt":
                            oFilter=string.Format("{0} gt {1}", item.Field, item.Value);
                            break;
                        case "gte":
                            oFilter=string.Format("{0} ge {1}", item.Field, item.Value);
                            break;
                        case "lt":
                            oFilter=string.Format("{0} lt {1}", item.Field, item.Value);
                            break;
                        case "lte":
                            oFilter=string.Format("{0} le {1}", item.Field, item.Value);
                            break;
                    }
      
                    if (oFilter == null)
                        continue;
      
                    if (sb.Length == 0)
                    {
                        sb.Append(oFilter);
                    }
                    else
                    {
                        sb.AppendFormat(" {0} {1}", logic, oFilter);
                    }
                }
      
                if (sb.Length == 0)
                    return;
      
                QueryItems.Add("$filter", sb.ToString());
            }
     
            public override void OnActionExecuted(System.Web.Http.Filters.HttpActionExecutedContext actionExecutedContext)
            {
                HttpRequestMessage request = actionExecutedContext.Request;
                HttpResponseMessage response = actionExecutedContext.Response;
     
                IQueryable query;
                if (response != null && response.TryGetContentValue(out query))
                {
                    bool Changed = false;
     
                    System.Collections.Specialized.NameValueCollection QueryItems = request.RequestUri.ParseQueryString();
                    string select = QueryItems["$select"];
                    if (!string.IsNullOrWhiteSpace(select))
                    {
                        query = query.Select(string.Format("new ({0})", select));
                        Changed = true;
                    }
     
                    if (Changed)
                    {
                        var enumerator = query.GetEnumerator();
     
                        List<dynamic> l = new List<dynamic>();
                        while (enumerator.MoveNext())
                            l.Add(enumerator.Current);
     
                        actionExecutedContext.Response = actionExecutedContext.Request.CreateResponse(HttpStatusCode.OK, l);
     
                    }
                }
            }
        }
      
        public class GridFilter
        {
            public string Operator { get; set; }
            public string Field { get; set; }
            public string Value { get; set; }
        }
    }

  17. James
    James avatar
    20 posts
    Member since:
    Apr 2012

    Posted 15 Jun 2012 Link to this post

    Here's a slightly easier way to do all of this instead of translating:

    Copy the kendo.odata.datasource.js and make your own.

    It requires only a few modifications to the events to get it retrieving the data properly and will send the right filter, order by etc. (you'll need to change it from $take to $top  as well). Then I just had to change the types to POST for insert, PUT for Update and DELETE for delete.

    The one issue that I couldn't solve with this approach was Kendos' broken DELETE. Unfortunately, jquery's ajax calls don't handle delete properly and pass the parameters as content data instead of in the URL. Kendo is unwilling to fix the jquery bug (which is in the jquery system as a bug btw) to conform to the RFC on REST (which I sent them proving that I'm right) so that on delete it sends the keys as multiple parameters in the URL. (i.e. <someurl>?ID=3&ID=5&ID=10 etc.)

    This complete lack of willingness to fix a clear bug as per the RFC guidelines was why I ultimately decided to drop Kendo when I realized that there was no interest in doing things right and standards compliance (the other issue is the combo can't handle null properly and there is no willingness to fix the obvious issue with how they're handing it.)

    So you'll still have to wire up an event on the delete instead of just passing a URL that will take the keys in the data and pass them back properly.

    Also to make your life easier you might want to consider making all of your Insert,updates,deletes into <someMethod>(IEnumerable<> Values) instead of just single values so that batch works. You can then override the functions in your odata substitute to make sure they always pass a json array instead of a single object. (and if you get back a single object from a get or something convert it to an array which kendo will understand in the same method)

    Sorry I don't have the code. I deleted it when I dropped Kendo.

    Also an FYI: Microsoft just figured out what I've been telling them for months. They pulled the crappy odata support they had in the latest nightlies and are replacing it with the full odata support found in the RIA services. I expect that shortly we should have nightlies with that functionality and while it might not get into the release version next month it will be in shortly after that. Whether they're going to change to the noisy meta data or just return arrays with a line count, I don't know yet but it's worth watching because this issue is going to improve significantly in the next little while.
  18. Mike
    Mike avatar
    7 posts
    Member since:
    Sep 2009

    Posted 20 Jun 2012 Link to this post

    James,

    Sorry your experience let you to drop Kendo. I'm hoping I won't have to do the same.
    I was really not wanting to change the Kendo datasource, but like you mention it takes care of the problem at the root of the problem.

    I did end up implementing $inlinecount into the translating attribute as well (using the technique from the asp.net web stack source at http://aspnetwebstack.codeplex.com/SourceControl/changeset/view/88372a0b4ab9#src%2fMicrosoft.Web.Http.Data%2fQueryFilterAttribute.cs)

    Everything works great but at this point it's just experimenting. Hopefully the RIA Services style odata will show up in a release soon.

    Thanks for the tip on DELETE. Haven't got there yet.

    Thanks,
    Mike
Back to Top
Kendo UI is VS 2017 Ready