Entity Framework, LINQ, and WCF RIA Services ordering tip

I thought I would share the following scenario with the hope that you can learn from my mistakes and do better. I have a screen that loads an account record. Based on that account, a hierarchical tree is loaded with supporting information in the bottom portion of the screen. The following is a screen shot of the screen with some really bizarre ordering in the tree:

If we look at the query that was used to load the hierarchy, we see the following:

[Query()]
public IQueryable ReturnBillsAndReturnsByOrganizationId(int organizationId)
{
	return this.ObjectContext.Organization.Where((c) => c.OrganizationId == organizationId)
	    .OrderBy(x => x.SubsystemName)
	    .OrderBy(x => x.BillType)
	    .OrderByDescending(x => x.FiscalYear)
	    .OrderByDescending(x => x.FiscalPeriod);
}

What I am trying to do is sort the tree in the following order:
Subsystem Name (ASC), BillType (ASC), Fiscal Year (DESC), Fiscal Period (DESC). Perhaps on first glance the syntax looks correct but if we refer to the documentation we learn some important pieces of information:

MSDN Queryable.OrderBy

MSDN Queryable.ThenBy

If we review the documentation, we will see that anytime we call OrderBy or OrderByDescending we get an IOrderedQueryable object. This means that every time we call OrderBy or OrderByDescending we are re-creating the IOrderedQueryable object. Clearly, this is not what we want to do.

If we review the documentation for ThenBy, we see that it requires a parameter of type IOrderedQueryable as the type of object to perform the operation. It then returns a now fully sorted object type of IOrderedQueryable. This is exactly what we are looking for. This works for both the ThenBy and ThenByDescending operations.

Thus it is necessary that our query looks like this:

[Query()]
public IQueryable ReturnBillsAndReturnsByOrganizationId(int organizationId)
{
	return this.ObjectContext.Organization.Where((c) => c.OrganizationId == organizationId)
		.OrderBy(x => x.SubsystemName)
		.ThenBy(x => x.BillType)
		.ThenByDescending(x => x.FiscalYear)
		.ThenByDescending(x => x.FiscalPeriod);
}

Finally, with this change, our tree is now in the correct order and all is good with the universe:

It is very easy to assume that the logic of the syntax for the ordering is correct when trying to force it to work like T-SQL. This isn’t always the case and we must be careful or our results will be entirely not what we are looking for.

Hope this helps….