Enforce ordering of OData items even when $top is used

Question

I have a DbSet<Items> collection.

The primary key is a Guid. I don't want to order by this primary key. I want to order by an editable decimal property named "Order".

The code I have is very simple, and it works great until the user puts a "$top" parameter into the request:

public class ItemsController : ApiController
{
    protected DbContext ctx = // ...

    // GET api/documents
    [EnableQuery()]
    public IQueryable<Item> Get()
{
    return ctx.Items.OrderBy(o => o.Order).AsQueryable();
}

When the user puts "$top" into the query string, the order gets all messed up (it presumably forces the ordering to be done by the primary key, for consistent paging results -- however, in my situation, this is having the opposite effect, it's preventing me from having consistent paging results).

I've tried moving .AsQueryable() to be earlier in the query (before the .OrderBy(...) clause), I've tried it without the .AsQueryable(), I've tried it with two AsQueryables, etc.

There are going to be a lot of items in this table, so it needs to be done via an IQueryable (enumerating all of the items on the web server via IEnumerable is not an option here).

The only thing that has worked so far is passing in "$orderby=Order" from the client, but I don't want to force that (seems like it will get forgotten easily).

1.) Is there anything I can do to make ordering by my Order property the default behavior here?

2.) Or failing that, is there anyway to trick WebApi / OData into thinking that a custom "$orderby=Order" clause was specified?

Answer 1

To override default sort order, you need to set property EnsureStableOrdering of EnableQueryAttribute to false, like describe here:

A true value indicates the original query should be modified when necessary to guarantee a stable sort order. A false value indicates the sort order can be considered stable without modifying the query. Query providers that ensure a stable sort order should set this value to false. The default value is true.

So in your code, changes the action attribute like this:

// GET api/documents
[EnableQuery(EnsureStableOrdering = false)]
public IQueryable<Item> Get()
{
    return ctx.Items.OrderBy(o => o.Order).AsQueryable();
}

Answer 2

You can manually invoke the odata in your controller. This should create the proper sorted IQueryable and then apply the $top and any other odata like $filter and $skip. Now you don't have to return an IQueryable which was causing the problem because the actual query was being executed later in the pipeline.

public class ItemsController : ApiController
{
    protected DbContext ctx = // ...

    public IEnumerable<Item> Get(ODataQueryOptions<Item> odata)
    {
        var collection = ctx.Items.OrderBy(o => o.Order);

        if (odata == null)
        {
            //return a default max size of 100
            return collection.Take(100).ToList();
        }

        var results = odata.ApplyTo(collection.AsQueryable()) as List<Item>;

        //still provide a max incase the $top wasn't specified.
        //you could check the odata to see if $top is there or not.
        return results.Take(100);
    }
}

More information can be found in the WebApi documentation.