Skip to content

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

Updated: at 02:12 PM

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!

Check out the ORM (Object Relational Mapper) PRISMA. The database access method I use in all my projects