>LINQ Query Operators – Part III

>

Ordering Operators:

Ordering operators like T-SQL "Order By" is used for ordering sequences in different ways. There are 5 ordering operators:

  1. OrderBy
  2. OrderByDescending
  3. ThenBy
  4. ThenByDescending
  5. Reverse

Except the last operator Reverse() all those are used as:

operator( keySelector ) or operator( keySelector, customComparer )  – customComparer is for custom sorting and not supported by L2S (Reverse() is not supported either).

You should have noticed that "descending" is not a parameter but separate methods exist to sort descending.

OrderBy: Orders the sequence by a given "keySelector". i.e:

Linq
T-SQL
Customers.OrderBy( c => c.ContactName )
Select * from Customers order by ContactName

 

Seconds overload accepts a comparer. Here is a sample that sorts orders by :

// In LinqPad select C# Program from language combo
// Delete default code
// Copy Paste or better hands on, write yourself
// and press F5 to run

void Main()
{
// This overload is not supported by L2S
// Getting into a local list first
// We would review later what does "local" mean
// for now just consider queries on ordList 
// is not Linq To SQL 
// (sort of you are querying a local cursor which
// you created by a remote call to SQL server)
 
var ordList = Orders.Select( o => 
                  new {
                           OrderId = o.OrderID,
                           OrderDate = o.OrderDate
                         }).ToList();
 
// creating an instance of custom comparer
// and passing it as a parameter
// final Select projection is about showing
// the result of sort
// Note that the sort is by "Day Of Week" enumeration
// and not by "WeekDay" alphabetically

var myCustomList = 
               ordList
                  .OrderBy( o => 
                                 o.OrderDate, 
                                 new OrderComparer() ) 
                 .Select( o => new {
                            o.OrderId,
                            o.OrderDate, 
                            o.OrderDate.GetValueOrDefault().DayOfWeek} );

myCustomList.Dump();
}

// Here is our custom sorting class
// It accepts parameters of type nullable DateTime
// and returns comparison result based on
// DayOfWeek – like DOW() in VFP

public class OrderComparer : IComparer<DateTime?>
{
   public int Compare(DateTime? x, DateTime? y)
   {
     return 
        x.GetValueOrDefault().DayOfWeek
         .CompareTo( 
        y.GetValueOrDefault().DayOfWeek ); 
    }
}

 

OrderByDescending: Same as OrderBy() except that it sorts in descending order (it was obvious,isn’t it).

ThenBy, ThnenByDescending: ThenBy and ThenByDescending are used to sort on multiple columns. Hmm why would you need something like this? Can’t you simply use chain of OrderBy(), OrderByDescending() operators? Of course you can but I think these were added to prevent you from writing it the way you intended (like in natural English you would be saying order by X then by Y then by Z). Probably this is best explained by a sample. We want to sort customers by their country and then by company name. It is very easy to write it as in first query and fail to get what we really intended.

Linq Corresponding SQL
Customers
   .OrderBy(c => c.Country)
   .OrderBy(c => c.CompanyName)
   .Select( c => new {c.Country, c.CompanyName} )
Select [t0].[Country], [t0].[CompanyName]
FROM [Customers] As [t0]
ORDER BY [t0].[CompanyName], [t0].[Country]
Customers
   .OrderBy(c => c.CompanyName)
   .OrderBy(c => c.Country)
   .Select( c => new {c.Country, c.CompanyName} )

SELECT [t0].[Country], [t0].[CompanyName]
FROM [Customers] AS [t0]
ORDER BY [t0].[Country], [t0].[CompanyName]

Customers
   .OrderBy( c => c.Country)
   .ThenBy  ( c => c.CompanyName)
   .Select( c => new {c.Country, c.CompanyName} )

SELECT [t0].[Country], [t0].[CompanyName]
FROM [Customers] AS [t0]
ORDER BY [t0].[Country], [t0].[CompanyName]

In SQL the order of columns dictate it and it is easy to write exactly what we want ( order by country and then by company ). However in Linq method syntax, keep in mind with each and every chained method we get a new intermediate sequence as a result ( and you could see how a series of methods is working by attaching a "Dump()" to each of those methods ). i.e:

Customers
  .Take(10).Dump("Got top 10")
  .Select( c => new {c.Country, c.CompanyName} ).Dump("Projected to 2 columns: Country,Company")
  .OrderBy( c => c.Country).Dump("Sorted on Country")
  .OrderBy( c => c.CompanyName).Dump("Sorted on Company")

 

As you can see we are first sorting on country and "then by" without a ThenBy() operator on company. The net effect we are sorting on company name only. ThenBy() operator excellently translates our intention about "by Country  THEN BY  Company Name".

Reverse: On a local sequence reverses the sequence as its name suggest. i.e:

int[] numbers = {1,2,3,4,5};
numbers.Reverse().Dump();

string reversed = new string(
  "Reverse Me".Reverse().ToArray()
  );

Console.WriteLine(reversed);

About cetinbasoz

A developer working primarily with C#,F#,VFP,SQL server, Silverlight,Linq ...
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s