Iqueryable Sorting Logic and Why Question..

10 posts, 0 answers
  1. Kemal
    Kemal avatar
    15 posts
    Member since:
    Apr 2017

    Posted 19 Dec 2018 Link to this post

    Hi.

    The problem is about sorting.

    It does not create a query by my own method.

    I want to understand why. Because it doesn't make sense to me. This is very easy sample;

    var db = new TicketSystemEntities();
    var issues = (from a in db.testTable
                        //orderby a.name ascending
                  select new
                  {
                      a.name,
                      a.guid,
                  }).OrderByDescending(f => f.name);
    //issues = issues.OrderByDescending(k => k.name);
    return Json(Test().ToDataSourceResult(request), JsonRequestBehavior.AllowGet);

     

    Three ordering types not ordering.. Sql profiling result is;

    ORDER BY row_number() OVER (ORDER BY [xxxx].[name] ASC)

     

    So.. OrderByDescending not used.. When i change order of fields;

    a.guid,
    a.name,

     

    Profiling is changing like this;

    ORDER BY row_number() OVER (ORDER BY [xxxx].[guid] ASC)

     

    So, system getting first field for ordering with "ASC".. Not "DESC".

    When i use Sort property in DataSource in razor, its working;

    .DataSource(dataSource => dataSource
      .Ajax()
      .Sort(s=>s.Add(d=>d.name).Descending())
      .Model(model => model.Id(p => p.guid))
      .PageSize(10)
      .Read(read => read.Action("y", "x"))
    )

     

    When i use AsEnumerable;

    db.testTable.OrderBy(o => o.name).AsEnumerable().Select(x => x);

     

    its working with AsEnumerable or ToList().

    it just doesn't work for the telerik Grid. In dropdown or other telerik modules, iqueryable queries can run sequentially.

    I want to do the sorting with the controller.

    First time I encounter such a problem.

    Is there an explanation for this problem?

  2. Tsvetina
    Admin
    Tsvetina avatar
    2481 posts

    Posted 20 Dec 2018 Link to this post

    Hi Kemal,

    The ToDataSourceResult extension method applies sorting automatically based on the parameters passed by the Grid. I assume this overwrites your sort expression. You can read more about this here: Ajax Binding(steps 7 and 8).

    When you execute ToDataSourceResult on an IQueryable, it applies the needed expressions on the queryable object and they are executed in the database. If you first call ToList or AsEnumerable, the query is executed before running ToDataSourceResult(), so the method performs the data operations in-memory and is not in conflict with your custom sorting.

    To combine your sorting with the data operations triggered by the Grid, you can use custom binding. This demo shows an example of custom binding:
    Grid / Custom ajax binding

    Alternatively, you can use client-side operations in the Grid, or use a default sort expression in the Grid DataSource to apply the default sort order, as you mentioned.


    Regards,
    Tsvetina
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  3. Kemal
    Kemal avatar
    15 posts
    Member since:
    Apr 2017

    Posted 21 Dec 2018 Link to this post

    Hi Tsvetina,

    Thank you for your nice reply and nice demo. I understand that is working with this logic and I know my research. Let's change example to anonymous;

    DataLayer

    public IQueryable X{
     var orders = dataContext.Orders.Select(o => new
     {
        OrderID = o.OrderID,
        ShipCity = o.ShipCity,
        ShipCountry = o.ShipCountry,
        ShipName = o.ShipName
     });
    }

    BusinessLayer

    public IQueryable X()
    {
        return _ticketDl.X();
    }

    Interface

    IQueryable X();

    Controller

    public JsonResult Read([DataSourceRequest] DataSourceRequest request)
    {
        var data = _ticketBl.X();
        return Json(data.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
    }

     

    i want to do this;

    public IQueryable X{
    var orders = dataContext.Orders
    //.OrderByDescending(o => o.OrderID)
    .Select(o => new
     {
        OrderID = o.OrderID,
        ShipCity = o.ShipCity,
        ShipCountry = o.ShipCountry,
        ShipName = o.ShipName
     });
     
     orders = orders.OrderByDescending(o => o.OrderID);

    return orders

    }

    Request have no any sort rule/rules. is this DataSourceRequest request overwrites my query result at controller?

    The more interesting, my teammate using .orderByDescending on query and returning IQueryable result from data-layer to grid and the answer is sequential. No other method.

  4. Plamen
    Admin
    Plamen avatar
    3079 posts

    Posted 25 Dec 2018 Link to this post

    Hi Kemal,

    Yes indeed when Ajax binding is used the ToDataSourceResult() method will page, sort, filter, and group the collection that is passed to it. The parameters for that are passed from the widget setting from the client. If this collection is already modified, the method returns an empty result. That is why in such cases in order to avoid such behavior the recommended approach is to use the Custom Binding.

    If you have still have questions or your scenario is somehow different please share the exact code used with the Kendo Grid too so we could try to simulate the case at our side and be more helpful. Sending a runnable dummy sample could be helpful too.

    Regards,
    Plamen
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  5. James
    James avatar
    3 posts
    Member since:
    Jul 2016

    Posted 01 Nov 2019 Link to this post

    Hello, 

    In continuation to the same issue...

    Can sorting not be simply ignored inside ToDataSourceResult extension method when  'Sorts' parameter is not set in DataSourceRequest object ? Instead  execute the iquery as is which has 'order by' clause already applied. Would that be difficult?

    if (DataSourceRequest.Sorts == 0)

        run iqueryable as is which has order by clause set

    else

        apply sorts

     

    This would be really helpful and avoid the need for any additional custom binding

  6. Kemal
    Kemal avatar
    15 posts
    Member since:
    Apr 2017

    Posted 03 Nov 2019 in reply to James Link to this post

    Hi i sorted in razor like this;

    .DataSource(dataSource => dataSource
        .Ajax()
        .Model(model =>
        {
            model.Id(p => p.id);
        })
        .Read(r => r.Action("Action", "Controller"))
        .Group(g => g.Add(d => d.x))
        .Sort(s => s.Add(d => d.x).Ascending()).Sort(s => s.Add(d => d.y).Ascending())
        .PageSize(100)
    ))

     

  7. James
    James avatar
    3 posts
    Member since:
    Jul 2016

    Posted 05 Nov 2019 in reply to Tsvetina Link to this post

    Hello, 

    In continuation to the same issue... 

    Can sorting not be simply ignored inside ToDataSourceResult extension method when  'Sorts' parameter is not set in DataSourceRequest object ? Instead, execute the iquery as is which has 'order by' clause already applied. 

    Would that be difficult? 

    if (DataSourceRequest.Sorts == 0)
        run iqueryable as is which has order by clause set
    else
        apply sorts

    This would be really helpful and avoid the need for any additional custom binding.

    ps: sorry replying to you as I am not sure if any of the admins are still active on this thread. hope to hear from you soon.

  8. Plamen
    Admin
    Plamen avatar
    3079 posts

    Posted 06 Nov 2019 Link to this post

    Hello James,

    Yes indeed this is the intended behavior - you can refer to this thread where the issue have been described and a suggested solution is provided - https://www.telerik.com/forums/todatasourceresult-doesn-t-preserve-the-default-order-when-sort-http-variable-is-not-set#kaSybWxxekmFz4LggE74WA

    Regards,
    Plamen
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  9. James
    James avatar
    3 posts
    Member since:
    Jul 2016

    Posted 08 Nov 2019 in reply to Plamen Link to this post

    Thanks Plamen for your reply. We are currently doing as suggested, applying manually.

    Am still wondering why this is intended behaviour and as to why it cannot be changed inside the extension method?

    At the end it is all string manipulation with IQueryable object. Not sure how you guys break up the query and execute inside the extension method, but using reflection surely you could append any more sorts needed for paging internally to what is currently set in the query string and then execute to return results.

    regards

  10. Plamen
    Admin
    Plamen avatar
    3079 posts

    Posted 12 Nov 2019 Link to this post

    Hi James,

    Thank you for the recommendation and for your thoughts. 

    It seems like a possible improvement indeed. I have forwarded your thoughts to our development team and I have found a similar request in our feedback portal. I have increased its priority and you a can follow its status here.

    If you have further questions or notes to add please let me know.

    Regards,
    Plamen
    Progress Telerik

    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Back to Top