Grid reorders server-side data even when no sort-order is specified

[Victor]

Hi,
We just realized that the grid component does some kind of reordering even when no sort order is defined. It sorts the same way everytime, but I cannot see any real pattern to what it sorts on.

We do the sorting server-side and breaking inside the razor view and expanding the list sent to the grid (iqueryable with direct connection to Entity Framework) shows the expected sort order. However, when the grid component has processed it the sort order has changed.

Hopefully this can be fixed soon!

/Victor

[Atanas Korchev]

Hi Victor,

Are you using Entity Framework? If yes the grid will always sort when you are using Entity Framework and need paging. Entity Framework refuses to page unsorted data. The grid is sorting by the first property available using the following code:

if (!sort.Any() && queryable.Provider.IsEntityFrameworkProvider())
{
  // The Entity Framework provider demands OrderBy before calling Skip.
   SortDescriptor sortDescriptor = new SortDescriptor
   {
         Member = queryable.ElementType.FirstSortableProperty()
   };
   sort.Add(sortDescriptor);
   temporarySortDescriptors.Add(sortDescriptor);
}

internal static string FirstSortableProperty(this Type type)
{
    PropertyInfo firstSortableProperty = type.GetProperties().Where(property => property.PropertyType.IsPredefinedType()).FirstOrDefault();

    if (firstSortableProperty == null)
    {
        throw new NotSupportedException(Exceptions.CannotFindPropertyToSortBy);
    }

    return firstSortableProperty.Name;
}

 This is behavior is by design and is needed in order to be able to page data in Entity Framework.

Regards,
Atanas Korchev
the Telerik team

[Victor]

Hi,

Thank you for that reply, clears up a lot! However, our problem is that we actually do sort the list before sending it to the grid component using code like this:

var viewModel = allProducts

                .OrderBy(p => p.Name)

                .Select(p => new ProductViewModel()

                {

                    Id = p.Id,

                    Name = p.Name,

//...

                });

return View(viewModel); //Or ToDataSource... for Ajax requests

After reading up on the issue the collection indeed needs to be sorted to be able to do paging using Entity Framework. Would it be possible to check if the collection is actually sorted before defaulting to sorting it? Using code similair to (code is tested, but not extensively):

public bool IsOrderedEnumerable(Type collectionType)

        {

            if (collectionType == null)

            {

                throw new ArgumentNullException("type");

            }

            foreach (Type interfaceType in collectionType.GetInterfaces())

            {

                if (interfaceType.IsGenericType)

                {

                    var typeDef = interfaceType.GetGenericTypeDefinition();

                    if (typeDef == typeof (IOrderedEnumerable<>) || typeDef == typeof(IOrderedQueryable<>) || typeDef == typeof(IOrderedQueryable))

                    {

                        return true;

                    }

                }

            }

            return false;

        }

We have found cases for both IOrderedEnumerable<>, and IOrderedQueryable<> but are not sure that IOrderedQueryable is ever applicable, probably you know this better than us.

Thanks again
/Victor

[Atanas Korchev]

Hello Victor,

 We are already making such checks (the first condition):

if (!sort.Any() && queryable.Provider.IsEntityFrameworkProvider()) 

 We however do not currently check for IOrderedEnumerable. If you use our extension method and specify some sort descriptors the automatic sort won't happen.

All the best,
Atanas Korchev
the Telerik team

[Victor]

Hi,
Absolutely. However our normal way of doing it (db => order (often by multiple fields) => create viewmodel => grid) will be auto-sorted at the moment. ToList() would also solve it, but is not feasible due to a large number of records. Specifying sort order in the grid config would work, but would (with single column sort order, due to usability discussed in another thread) only sort by one column.

I suppose setting a sort order to the most important column is the best solution for now, but it would be nice if this could be fixed eventually.

/Victor

[Atanas Korchev]

Hi,

 We will check if the check for IOrderedEnumerable is not too much of a performance hit and implement it if not.

All the best,
Atanas Korchev
the Telerik team

[Victor]

Hi,
Perfect, that would be really nice. I am sure there are other solutions to it as well, this is just one we have found works well in our scenarios. Worst case maybe there could be an option to tell via some options that the dataset is already ordered. Would be quite ad hoc though, since it is only relevant to Entity Framework users.

Another option would of course be to require the user to sort the dataset (or specify a sort order) when using Entity Framework and throw an exception otherwise (or let the default one fall through), but I suppose you have decided that that is a bad way to do it? For us a completely unsorted dataset is unthinkable, so for us that would be a reasonable requirement, but others might of course disagree.

Thanks again
/Victor

[Atanas Korchev]

Hello Victor,

 While investigating this issue I found out that the simple check if the queryable implements IOrderedQueryable is not sufficient. The IQueryable returned by Linq to Entities always implements IOrderedQueryable<T> regardless OrderBy has been used or not. Unfortunately I couldn't find a reliable way to tell if OrderBy is used or not.

Regards,
Atanas Korchev
the Telerik team

[Victor]

Hi again,

I see... That is unfortunate. I could investigate a bit more, but have given it some thought...

What would the harm be in following EF:s decition and let EF throw an error if the user has neither called .OrderBy(...) or added a custom sort order when configuring the grid? To me that seems like a much better solution than to add a slightly arbitrary sorting order just to be sure.

I mean - this issue would for sure be caught easily during development and since the error is pretty clear and well-documented (the EF error I mean) it would be easy to correct. Also it seems quite reasonable that a sort order should be specified when using paging.

What do you think?
/Victor

[Atanas Korchev]

Hello Victor,

 This is how it was originally implemented in Telerik Extensions for ASP.NET MVC. However customers started complaining that they don't want to sort their data before passing it to the grid. And the rest is history.

Regards,
Atanas Korchev
the Telerik team

[Victor]

Hi again,

I see. I understand even though it is unfortunate since it makes more advanced implementations a lot harder...

Anyway - after some testing  I can verify your findings, the resultset always implements IQueryable<T> unfortunately.

Another suggestion: how about catching the System.NotSupportedException thrown ("The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'." in current version of EF) and sort only then?

And if that is not possible, would it be possible to add an option to turn this behaviour off in some way? In general I would really like to see a way to set global options (paging, page size, filtering, grouping, etc), but even a local configuration option would be welcome here...


/Victor

[Atanas Korchev]

Hi Victor,

Could you please let me know why using a SortDescriptor to apply the sort is not an option in your case? How does your application code look like? It feels as if you have some sort of semi-custom binding implementation.

Regards,
Atanas Korchev
the Telerik team

[Victor]

Absolutely! As said we usually have relatively large resultsets (1000+++ records) and want some direct mapped db fields and some calculated fields. The calculated fields needs to be calculated in memory since the DB layer does not support the custom operations required. And of course we want to do the DB => memory transition on one page only, not on the full result set. Code can look something like this (you can also see the ToDataSourceResult fix):

private CreateProducerViewModel CreateSuppliersViewModel(IQueryable<Company> companies, DataSourceRequest request)

{

   //companies is an EF ObjectSet<Company>

    var viewModel = new ProducersViewModel();

    var producers = companies

        .OrderBy(p => p.Category.Name).ThenBy(p => p.Name).ThenBy(p => p.SomeOtherProperty)


        .Where(c => c.IsProducer)

        .Select(c => new ProducerViewModel()

            {

                Id = c.Id,

                Name = c.Name,

                // ....

                Products = c.Products.OrderBy(b => b.Name).Select(p => p.Name),

            });

    viewModel.TotalCount = producers.Count(); //For Kendo paging

    var result = producers

        .Take(request.PageSize)

        .ToList()

        .ToDataSourceResult(request);

    var producersList = result.Data.Cast<ProducerViewModel>().ToList();

    producersList.ForEach(p =>

                              {

                                  p.ProductsCount = p.Products.Count();

                                  p.ProductsList = String.Join(", ", p.Products); //In this example, this needs to be done in memory

                              });

    viewModel.Producers = producersList;

  

    return viewModel;

}

This is the kind of structure we are using right now. We use it from server and ajax controller functions, like this:

public ActionResult Index()

{

    var request = new DataSourceRequest();

    request.PageSize = 25; //Not required for ajax requests

    var viewModel = CreateSuppliersViewModel(companiesRepository.Query, request);

    return View(viewModel);

}

 

public ActionResult _Producers([DataSourceRequest]DataSourceRequest request)

{

    var viewModel = CreateSuppliersViewModel(companiesRepository.Query, request);

    return Json(viewModel.Producers.ToDataSourceResult(request));

}

Maybe there is a much better way to accomplish this, but this is what we are trying to do at least. Optimally we would like to specify each "thing" only once, such as page size, sort orders, etc, but right now we have not decided on the best route.

/Victor

[Atanas Korchev]

Hi Victor,

It seems that the only workaround is to specify the sort descriptors manually:

request.Sorts = new[] { new SortDescriptor { Member = "Category.Name" }, new SortDescriptor { Member = "Name" } };

On a side not your current implementation is not that bad. You should keep in mind though that it will not work if grouping or aggregates are involved because the following statement will fail:

var producersList = result.Data.Cast<ProducerViewModel>().ToList(); 

I am reluctant to add a try/catch block in order to handle this case. There are probably other valid reasons when NotSupportedException will be raised.

A global setting may work. I am just not sure how to name the damn thing - SortIfUnsortedWhenEntityFramework = true. 

 I tried to understand how EF detects unordered data but the code required parsing expression trees - something which was really complex.

Regards,
Atanas Korchev
the Telerik team

[Victor]

Hi Atanas,

I just realized from you post in the other thread that the code will not work using groupings or aggregates, which is of course not ok. Luckily, this is still for us still testing grounds before starting a full migrate from Telerik ASP MVC controls to KendoUI Asp MVC.

Anyway, I certainly see your reluctance for try/catch since it does not throw a specific exception, it was just a thought. Also, since it is EF specific the global setting also feels a bit odd/hacky. For me the best way would be to not handle the exception at all, since it is a design decision in EF and also EF specific. Every time you use EF and need paging, you need to use OrderBy(..) first, I really dont see why a specific UI component should change this even if I think the effort and workaround is very well intentioned.

For us however the auto-sort on paging would require workaround for almost every grid even though the data is already sorted. Also, we would want to set the sorts only when it has not been set in the gui, but that is of course possible to check. The problem is that it gets quite tangled in every grid configuration. And finally EF (linq) sorting is strongly typed where the Sorts array you suggested seems to be strings only.

Anyway, I think you have a good view of our position and reasons for them. Please post back once the implementation has been set so we can adjust accordingly.

[Atanas Korchev]

Hello Victor,

 What code did you use in Telerik Extensions for ASP.NET MVC? The implementation is exactly the same as in Kendo UI Complete for ASP.NET MVC. The same limitations exist in Telerik Extensions for ASP.NET MVC.

 As I explained earlier we can't really change that implementation because it would be a major breaking hange. If you believe that we should stop making an implicit sort please open a user voice request. We will decide whether to drop that based on other user's input.

Regards,
Atanas Korchev
the Telerik team

[Victor]

Hi again,

Very interesting question about the code for Telerik ASP MVC. We actually never solved this in a good way. Where it was really important we did a lot of ad-hoc processing (different in server and ajax functions), custom bloated viewmodels or custom extension methods, but for most parts in out production system we have skipped the non-db projection functions due to the amount of code needed. Since we found out that Telerik ASP MVC will be discontinued however, it felt more important to address the issue early here.

Anyway - if the behaviour is not changed, it would be very nice to have an opportunity to at least turn it off.

And for the user voice system - it is a very interesting solution to prioritizing input, but it feels like MVC requests will be lost amongst all others. Are you planning on at least creating a category for ASP MVC specific suggestions?

/Victor

[Daniel]

Hello Victor,

There is a separate category for the Kendo MVC but there are not any suggestions posted yet and so it will not be visible from the dropdown with the current items. When you try to submit a new item you should be able to see a category for ASP.NET MVC Wrappers. 

Regards,
Daniel
the Telerik team