Iqueryable Sorting Logic and Why Question..

[Kemal]

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?

[Tsvetina]

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

[Kemal]

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.

[Plamen]

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

[James]

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

[Kemal]

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

 

[James]

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.

[Plamen]

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

[James]

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

[Plamen]

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