OData Query Option top Forces Data To Be Sorted By Primary Key

I’ve recently started using Microsoft’s WCF Data Services which supports OData Services.  What this means is that we can access resources by simply specifying a URI.  This concept greatly simplified building an ORM layer on a web site, as well as creating the linkage between the server side data and the client side application, which in my case is usually a browser.

So, the issue this blog addresses is that if you form a URI with the parameter $top={anything}, your data will automatically be sorted.  The documentation for OData on top basically says that, but it could be clearer.  It says the following:

“If the data service URI contains a $top query option, but does not contain a $orderby option, then the Entries in the set needs to first be fully ordered by the data service.”

What actually happens is when you use the orderby clause, the data will be sorted 100% of the time for you, whether you do it or not.

I put a small example together that shows that.  I’ll briefly step through the parts of the code attached to this post that show that happening.  First, the results:

image

Here is the actual Visual Studio 2010 RC project you can run yourself:

Notice that when $top=5 is called, the data returned is sorted by the primary key.

Now, here are the details of the project that shows this happening.

Below is the complete code for the WcfDataService.svc.  Notice that the data is created in the SampleDataSource constructor and is created in non-key order.

using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Data.Services.Common;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
 
namespace WebApp
{
    [EntityPropertyMappingAttribute(
        "Title", SyndicationItemProperty.Title,
        SyndicationTextContentKind.Plaintext, true)]
    [DataServiceKey("Id")]
    public class SampleInfo
    {
        // The DataServiceKey attribute is necessary unless you name 
        // this ID
        public int Id { get; set; } 
        public string Title { get; set; }
    }
 
    public class SampleDataSource
    {
        private readonly List<SampleInfo> _sampleInfoList;
 
        public SampleDataSource()
        {
            _sampleInfoList =
                new List<SampleInfo>()
                    {
                        new SampleInfo {Id = 3, Title = "3"},
                        new SampleInfo {Id = 4, Title = "4"},
                        new SampleInfo {Id = 1, Title = "1"},
                        new SampleInfo {Id = 5, Title = "5"},
                        new SampleInfo {Id = 2, Title = "2"}
                    };
        }
 
        public IQueryable<SampleInfo> SampleInfoData
        {
            get { return _sampleInfoList.AsQueryable(); }
        }
    }
 
    public class WcfDataService : DataService<SampleDataSource>
    {
        // This method is called only once to initialize service-wide policies.
        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
            config.DataServiceBehavior.MaxProtocolVersion = 
                DataServiceProtocolVersion.V2;
        }
    }
}
 
I’ve created a very simple web page that includes some simple JavaScript to display the results of calling this WCF DataService with and without $top.  The page code is below.
 
<head runat="server">
    <title>Demonstration of OData Sort Based on Using $top</title>
 
     <script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.3.2.min.js"    
        type="text/javascript"></script>  
 
 
    <script type="text/javascript" >
        // Display the data with and without $top with OData
        $(document).ready(function () {
            loadData("/WcfDataService.svc/SampleInfoData","#resultTableNoODataParms");
            loadData("/WcfDataService.svc/SampleInfoData?$top=5", "#resultTableWithODataParms");
        });
 
        // Called after DOM is ready to call service and display table data
        function loadData(urlString,divName) {
            var url = urlString; // "/WcfDataService.svc/SampleInfoData";
            $.ajax({
                type: "GET",
                url: url,
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (msg) {
                    loadTable(msg.d,divName);
                }
            });
        }
 
        // display data at a given ID
        function loadTable(results,divName) {
            var table = '<table border=1><tbody><tr>';
            for (var post in results) {
                var row = '';
                row += '<td>' + results[post].Id + '</td>';
                table += row;
            }
            table += '</tr></tbody></table>';
            $(divName).html(table);
        }
    </script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h2>/WcfDataService.svc/SampleInfoData</h2>
    <div id="resultTableNoODataParms" />
    </div>
    <hr />
     <div>
    <h2>/WcfDataService.svc/SampleInfoData?$top=5</h2>
    <div id="resultTableWithODataParms" />
    </div>
 
    </form>
</body>
</html>

As you can see, adding the $top=5 causes the data to be sorted.

Hope this helps!

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. So let me get this straight. If you DO NOT specify a $orderby or $top option, then the results from OData will be listed in the order the items were inserted into the database?

  2. Hi Marcelo,
    The reason I did not expect the behavior is because I am use to SQL Server and how it handles Select top. In SqlServer, using select top does not force a sort.

  3. Just wanted to drop a note and confirm that this is indeed the case.

    The idea behind this decision is that we wanted clients to be able to ‘scan’ a complete feed by using a series of $top/$skip requests, but to do that we need to guarantee that the results are ordered by *something*.

    So if there isn’t an $orderby option, the system orders by primary key (which will give a stable order to all entries).

Your Comments

*

Protected with IP Blacklist CloudIP Blacklist Cloud

Follow

Get every new post delivered to your Inbox

Join other followers: