Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

October 30, 2010

Sorting Lists with Null Values

Filed under: C#,Lambda Expressions,VB.NET @ 2:04 am
When working with data, you often have values that are null. For example, you may allow entry of a user’s birthday, but not require entry. So some of your rows may contain a null date. Or your application may track a customer’s last order date, which will be null if you track potential customers that have not yet made a purchase.

When you sort a list that contains null values, the null values will be first in the list as shown below:

image

But what if you want the null values at the end and not at the beginning?

image

This post demonstrates one technique for sorting your null values to the bottom of the list.

First, some prerequisite code to build the list of business objects that are bound to the grid.

In C#:

Here is the Customer class:

public class Customer { public int CustomerId { get; set; } public string LastName { get; set; } public string FirstName { get; set; } public string EmailAddress { get; set; } public DateTime? LastOrderDate { get; set; } }

And here is the one line of code to build the list of customers:

    List<Customer> custList = new List<Customer> {new Customer() { CustomerId = 1, FirstName=”Bilbo”, LastName = “Baggins”, EmailAddress = “bb@hob.me”, LastOrderDate = new DateTime(2010,9,1)}, new Customer() { CustomerId = 2, FirstName=”Frodo”, LastName = “Baggins”, EmailAddress = “fb@hob.me”, LastOrderDate = null}, new Customer() { CustomerId = 3, FirstName = “Samwise”, LastName = “Gamgee”, EmailAddress = “sg@hob.me”, LastOrderDate = new DateTime(2010, 6, 15), }, new Customer() { CustomerId = 4, FirstName = “Rosie”, LastName = “Cotton”, EmailAddress = “rc@hob.me”, LastOrderDate = new DateTime(2010, 6, 15), } };

The code to bind this list to the grid is:

var sortedList = custList.OrderBy(c => c.LastOrderDate); dataGridView1.DataSource = sortedList.ToList();

In VB:

Here is the Customer class:

Public Class Customer Public Property CustomerId As Integer Public Property LastName As String Public Property FirstName As String Public Property EmailAddress As String Public Property LastOrderDate As DateTime? End Class

And here is the one line of code to build the list of customers:

Dim custList As New List(Of Customer) From {New Customer() With {.CustomerId = 1, .FirstName = “Bilbo”, .LastName = “Baggins”, .EmailAddress = “bb@hob.me”, .LastOrderDate = New DateTime(2010, 9, 1)}, New Customer() With {.CustomerId = 2, .FirstName = “Frodo”, .LastName = “Baggins”, .EmailAddress = “fb@hob.me”, .LastOrderDate = Nothing}, New Customer() With {.CustomerId = 3, .FirstName = “Samwise”, .LastName = “Gamgee”, .EmailAddress = “sg@hob.me”, .LastOrderDate = New DateTime(2010, 6, 15)}, New Customer() With {.CustomerId = 4, .FirstName = “Rosie”, .LastName = “Cotton”, .EmailAddress = “rc@hob.me”, .LastOrderDate = New DateTime(2010, 6, 15)} }

The code to bind this list to the grid is:

Dim sortedList = custList.OrderBy(Function(c) c.LastOrderDate) DataGridView1.DataSource = sortedList.ToList()

The first set of code defines the customer business object. Note how the LastOrderDate property is defined as a Nullable structure. This allows the date to hold a null value. Both the C# and VB examples use auto-implemented properties, which provide a shortened property syntax.

See this link for more information about auto-implemented properties.

The second (very long) line of code builds the list of customers. Both the C# and VB code build the list using collection initializer syntax.

The last two lines of code first sort the list using a Lambda expression and then bind that sorted list to a DataGridView.

If you are not familiar with Lambda expressions, see an overview here.

But this example sorts in the default order, with null values first. One way to sort null values to the end is to perform a descending sort. But then all of the rows are sorted in the opposite direction.

If you want an ascending sort for all of the data, but nulls at the end, you can use multiple sorts as shown below.

In C#:

var sortedList = custList .OrderBy(c => !c.LastOrderDate.HasValue) .ThenBy(c => c.LastOrderDate); dataGridView1.DataSource = sortedList.ToList();

In VB:

Dim sortedList = custList. OrderBy(Function(c) Not c.LastOrderDate.HasValue). ThenBy(Function(c) c.LastOrderDate) DataGridView1.DataSource = sortedList.ToList()

This code first sorts by the LastOrderDate’s HasValue property. The HasValue property of the Nullable structure defines whether or not the property has a value. So if the property has a value, HasValue is true and if the property is null, HasValue is false. This code checks for “not HasValue”, so if the  property is null, the expression is true.

Since true values are sorted to the end in an ascending sort, the dates with values will be first in the sort order, then the null values.

The secondary sort (ThenBy), then sorts by the LastOrderDate. This provides the list with the rows in ascending date order, but with the null values last in the list.

Use this technique any time you need to sort a list and ensure that all null values are at the end of the resulting sorted list.

Enjoy!

3 Comments

  1.   EL — October 31, 2010 @ 3:49 am    Reply

    Hai Deborah nice to read your article.

    Btw I am a very begginer in VB. Could You give a short example about the data if it comes from a table in a database, for example SQL Express 2008?

    Thanks in advance.

  2.   DeborahK — October 31, 2010 @ 8:40 pm    Reply

    I have an example of sorting a DataTable here:

    http://msmvps.com/blogs/deborahk/archive/2009/07/23/linq-sorting-a-datatable.aspx

    Hope this helps.

  3.   net grid — November 18, 2012 @ 11:44 pm    Reply

    Data sorting information is contained in grid headers. Users may set single or multiple sorting in one or multiple headers. On programming level, sorting looks as follows:

    //Get top-level header
    Header header = grid.Headers[0];

    header[“Product”].SortDirection = SortDirection.Ascending;
    header[“Price”].SortDirection = SortDirection.Descending;
    more help and see the example on tutorial read dapfor. com

RSS feed for comments on this post. TrackBack URI

Leave a comment

*

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs