Posted by gabriel, Mon Aug 11 12:24:00 UTC 2008

This article shows a tip on how you can do sorting using the LINQ OrderBy with Typed DataSets.

For our example I’ll use the the Northwind Database and it’s Customers table.

I’ll use two columns in this table. One is the ContactName which doesn’t have any null values and the Region column which has null values.

First lets populate the DataTable we want to query using TableAdapter I had previously generated in the DataSet.

CustomersTableAdapter ta = new CustomersTableAdapter();
DataSetNorthwind.CustomersDataTable dt = ta.GetData();

I want to use LINQ select all values ordering them by the Region column (which may be null). So here is the initial idea:

            var query = from r in dt.AsEnumerable()
                        orderby r.Region
                        select r;

Ok, no compilation errors but when you run the query you will get an error:

The value for column ‘Region’ in table ‘Customers’ is DBNull. -> System.InvalidCastException: ...

If you think about it this is expected. The nullable columns in the DataSet all have an IsNull method to check if the column is null or not and avoid this kind of error. We are going to use the IsRegionNull() method in our favor to help us solve the problem:

var query = from r in dt.AsEnumerable()
        orderby (r.IsRegionNull() ? "" : r.Region)
        select r;

I used the ternary operator to check if the column region is null or not. If it is then I’ll use an empty string as the value, if it’s not null I can call the Region property to get the value.

To see the ending result you can print all the values:

foreach (DataSetNorthwind.CustomersRow row in query)
{
     Console.WriteLine("{0} - {1}", row.ContactName, 
          row.IsRegionNull() ? "" : row.Region);
}

Of course you could have filtered out the rows with null regions, but the goal here is to show what you can do to order the rows when you have a column with values that may be null.

Filed Under: Tips and Tutorials | Tags: .net DataSet dataset linq