LINQ: OrderBy on multiple Fields

by Arnold Matusz 2 11 2008

Most people think that by calling OrderBy().OrderBy() will solves the problem of ordering a sequence by multiple fields. Well I'm dissapointed to announce this is not true because the second call overwrites the ordering clause specified in the first one!

First of all lets analyze the architecture of a LINQ ObjectQuery. The important peice is what it actually returns, namely: an IQueriable object! This IQueriable interface implements the IEnumarble interface which we are normally used to use when creating an ObjectQuery.

//sample ObjectQuery
IEnumerable<TimeSheets> timesheets = entities.TimeSheets
     .Where(mts => mts.aspnet_Users.UserId == UserId).ToList()
     .Where(mts => mts.TimeStamp.Value.Date.Month == Month)
     .Where(mts => mts.TimeStamp.Value.Date.Year == Year);

But because I normally use the IEnumerable interface as the expected outcome of an ObjectQuery I got a bit sidetracked when I needed to OrderBy() on multiple Fields. 

//sample ObjectQuery with OrderBy clause (only one field)
IEnumerable<TimeSheets> timesheets = entities.TimeSheets
     .Where(mts => mts.aspnet_Users.UserId == UserId).ToList()
     .Where(mts => mts.TimeStamp.Value.Date.Month == Month)
     .Where(mts => mts.TimeStamp.Value.Date.Year == Year);
     .OrderBy(mts => mts.TimeStamp); 

The fact of the matter is that OrderBy() returns an IOrderedEnumerable which then exposes the Methods: ThenBy() and ThenByDescending(). This means that we can OrderBy on multiple Fields by chaining the OrderBy() and ThenBy() together.

Here's a little example of where this comes in handy. In my example I need to return all the TimeSheet entries of a User for a specific Month and a Year then I need to order all elements by the date the entry was introduced on and by the TaskID that the time sheet entries refer to.

/// <summary>
/// Returns all recorded time sheet entries for a Membership User (UserID), Month and Year
/// </summary>
/// <param name="UserId">The UserID of the Membership User</param>
/// <param name="Month">The specific Month for the timesheets</param>
/// <param name="Year">The specific Year for the timesheets</param>
/// <returns>IEnumerable<TimeSheets> - the List of 
/// Timesheets for a Membership User (UserID), Month and Year</returns>
public static IEnumerable<TimeSheets> TimeSheetsOf(Guid UserId, int Month, int Year)
{
     EDMEntities entities = new EDMEntities();
     IEnumerable<TimeSheets> timesheets = entities.TimeSheets
        .Where(mts => mts.aspnet_Users.UserId == UserId).ToList()
        .Where(mts => mts.TimeStamp.Value.Date.Month == Month)
        .Where(mts => mts.TimeStamp.Value.Date.Year == Year);
     foreach (TimeSheets ts in timesheets)
        if (!ts.ProjectTasksReference.IsLoaded) ts.ProjectTasksReference.Load();
     return timesheets.OrderBy(mts => mts.TimeStamp)
                      .ThenBy(mts => mts.ProjectTasks.TaskID);
}

* Note that the we need to use the .ToList() call because we cannot use .Date.Year in LINQ to Entities, or else the following:  System.NotSupportedException will be thrown: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

DZone it Digg it Submit to StumbleUpon Submit to Technorati Submit to reddit.com Submit to del.icio.us Submit to NewsVine Submit to Furl Submit to BlinkList

Tags: , , ,

ADO.NET | C# | LINQ | Programming

Comments

11/3/2008 5:50:14 AM #

trackback

Trackback from DotNetKicks.com

LINQ: OrderBy on multiple Fields

DotNetKicks.com |

7/2/2009 6:05:12 PM #

aspnet

Thanks a lot.i wanna sort a grid using multiple fields.This article helped me to accomplish my task easier.

aspnet India |

1/13/2010 11:19:28 PM #

fernando

Nice post!

fernando Brazil |