Controling your sorts under LINQ to SQL

I wrote an article recently about sorting generic arrays and changing direction easily in custom comparer classes here. As this is a device centric blog, using generic comparer classes is still the only (best) way to sort your collections in .NET after they have been read from SQL Server.

I said at the end of the above post that I'd write a quick post on how to do this using LINQ to SQL - bearing in mind this is *not* supported on the .NET CF 3.5. It is very simple and in fact alot easier with LINQ than in the above post. Usually new technologies come with an attached deep learning curve but with LINQ to SQL the learning curve is not that great.

In my last post we had a very simple customer object with a couple of columns. I've modeled this using the Entity Framework in VS 2008:


Entity Framework modeled table in Visual Studio 2008.

As you can see we have the same properties available to us as we did in the comparer post that we created manually. It is a very simple table with no relations.

Note, in order to create a LINQ to SQL mapping, you can right click your project in VS and choose "Add new item" from the context menu, then select LINQ to SQL Classes template.


Creating a LINQ to SQL mapping

I'm not going to go into too much detail on mappings and the various ways it can be done as the aim of this post is to show the syntax of sorting customer as opposed to that of using a comparer class.

If you've done any LINQ to SQL you'll know about the DataContext class. So now we have created our model VS has kindly created the object model for us so we can go right ahead and use it:

static class Program
{

[STAThread]

static void Main()

{

TestDevDataContext testDev = new TestDevDataContext();
var query2 = from c in testDev.Customers
            select c;
Console.WriteLine("Before sort");
DisplayCollection(query2.ToList());

var query3 = from c in query2
             orderby c.City ascending
             select c;

Console.WriteLine("After sort - Ascending");
DisplayCollection(query3.ToList());
var query4 = from c in query2
             orderby c.City descending
             select c;
Console.WriteLine("After sort - Descending");
DisplayCollection(query4.ToList());
}

public static void DisplayCollection(List<Customer> customers)
{
foreach (Customer customer in customers)
{
    Console.WriteLine(string.Format("Name: {0}, City: {1}",
                   customer.Name, customer.City));
}
Console.WriteLine();
}
}

The code is greatly simplified in this version over our generic array and comparer and with this example we need not write multiple comparers for each column we wish to sort on. Our custom DataContext class is named TestDevContext which is my test database on this machine (Vista x64).


Output of above

For interest, the SQL generated looks like the following:

SELECT [t0].[CustomerId], [t0].[Name], [t0].[AddressLine1], [t0].[AddressLine2], [t0].[AddressLine3], [t0].[City], [t0].[County], [t0].[PostalCode], [t0].[Country]
FROM [dbo].[Customer] AS [t0]

SELECT [t0].[CustomerId], [t0].[Name], [t0].[AddressLine1], [t0].[AddressLine2], [t0].[AddressLine3], [t0].[City], [t0].[County], [t0].[PostalCode], [t0].[Country]
FROM [dbo].[Customer] AS [t0]
ORDER BY [t0].[City]

SELECT [t0].[CustomerId], [t0].[Name], [t0].[AddressLine1], [t0].[AddressLine2], [t0].[AddressLine3], [t0].[City], [t0].[County], [t0].[PostalCode], [t0].[Country]
FROM [dbo].[Customer] AS [t0]
ORDER BY [t0].[City] DESC

As you can see each sort goes back to the database to get a new set of data, so SQL Server is used in this case to actually do the sort.