Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 23, 2009

Linq: Sorting a DataTable

Filed under: C#,Data,Lambda Expressions,LINQ,VB.NET @ 6:24 pm

Last night, my husband (who is also a .NET developer) asked me about sorting a DataTable by user-defined columns. (Yes, we are a pretty exciting couple!)

Most developers that work with DataTables know how to sort the DataTable using a DataView.Sort. If you are interested in that technique, you can view the msdn documentation here. This post is about sorting using Linq.

NOTE: Be sure to set a reference to System.Data.DatasetExtensions.

If you know ahead of time which columns of the DataTable should be sorted, you can use LINQ to DataTables like this.

In C#:

var query = from c in dt.AsEnumerable()
            orderby c.Field<DateTime?>("LastPurchaseDate"),
                    c.Field<string>("LastName") descending
            select c;
DataView dv   = query.AsDataView();

In VB:

Dim query = From c In dt.AsEnumerable _
            Order By c.Field(Of DateTime?)("LastPurchaseDate"), _
                     c.Field(Of String)("LastName") Descending
Dim dv As DataView = query.AsDataView

The dt variable represents the table you wish to sort. The AsEnumerable is an extension method on the DataTable that allows you to use Linq with it. The Order By clause takes any number of columns. You must specify the data type and then the column name or index. To sort descending, use the Descending keyword.

A nullable DateTime (represented by DateTime?) ensures that the code correctly handles any null values in the table.

In the above examples, the data is sorted first by LastPurchaseDate (ascending) and then by LastName (descending).

The resulting DataView can be bound to a grid or other control.

If you prefer Lambda expressions, you can do this same sort as follows:

In C#:

var query2 = dt.AsEnumerable()
          
.OrderBy(c=> c.Field<DateTime?>("LastPurchaseDate"))
           .ThenByDescending(c=> c.Field<string>("LastName"));
DataView dv2   = query.AsDataView();

In VB:

Dim query2 = dt.AsEnumerable _
    .OrderBy(Function(c) c.Field(Of DateTime?)("LastPurchaseDate")) _
    .ThenByDescending(Function(c) c.Field(Of String)("LastName"))
Dim dv2 As DataView = query.AsDataView

This code performs the same sort as the prior examples.

But neither of these techniques work well if you want the user to select any number of columns to use for the sort. In that case, you need something a little more full-featured.

The first step to building a more generalized sort is to build your own comparer class.

In C#:

private class RowComparer : IComparer<DataRow>
{
    public Dictionary<int, SortOrder> SortColumns { get; set; }

    public int Compare(System.Data.DataRow x, System.Data.DataRow y)
    {
        int returnValue  = 0;
        foreach (int key in SortColumns.Keys)
        {
            int compareResult ;

            // Check for nulls
            if (x.ItemArray[key] == DBNull.Value
                       && y.ItemArray[key] == DBNull.Value)
                compareResult = 0;
            else if (x.ItemArray[key] == DBNull.Value)
                compareResult = -1;
            else if (y.ItemArray[key] == DBNull.Value)
                compareResult = 1;
            else
            {
                // Execute the compare based on the column type
                if (x.Table.Columns[key].DataType.Name ==
                                            typeof(Decimal).Name)
                    compareResult =
                         Decimal.Compare((decimal)x.ItemArray[key],
                                         (decimal)y.ItemArray[key]);

                else if (x.Table.Columns[key].DataType.Name ==
                                            typeof(DateTime).Name)
                    compareResult =
                         DateTime.Compare((DateTime)x.ItemArray[key],
                                          (DateTime)y.ItemArray[key]);
                else
                    // Compare anything else as a string
                    compareResult =
                         String.Compare(x.ItemArray[key].ToString(),
                                        y.ItemArray[key].ToString());
            }

            if (compareResult != 0)
            {
                returnValue =
                   SortColumns[key] == SortOrder.Ascending ?
                                  compareResult: -compareResult;
                break;
            }
        }
        return returnValue;
    }
}

In VB:

Public Class RowComparer
    Implements IComparer(Of DataRow)

    Private _sortColumns As Dictionary(Of Integer, SortOrder)
    Public Property SortColumns() As Dictionary(Of Integer, SortOrder)
        Get
            Return _sortColumns
        End Get
        Set(ByVal value As Dictionary(Of Integer, SortOrder))
            _sortColumns = value
        End Set
    End Property

    Public Function Compare(ByVal x As System.Data.DataRow, _
         ByVal y As System.Data.DataRow) As Integer _
         Implements System.Collections.Generic.IComparer( _
                            Of System.Data.DataRow).Compare
        Dim returnValue As Integer = 0
        For Each key As Integer In SortColumns.Keys
            Dim compareResult As Integer

            ‘ Handle DBNull.
            Dim xValue As Object = _
              If(x.Item(key) Is DBNull.Value, Nothing, x.Item(key))
            Dim yValue As Object = _
              If(y.Item(key) Is DBNull.Value, Nothing, y.Item(key))

            ‘ Execute the appropriate compare based on the column type
            Select Case x.Table.Columns(key).DataType.Name
                Case GetType(Decimal).Name
                    compareResult = _
                        Decimal.Compare(CType(xValue, Decimal), _
                                      
CType(yValue, Decimal))

                Case GetType(DateTime).Name
                    compareResult = _
                        DateTime.Compare(CType(xValue, DateTime), _
                                         CType(yValue, DateTime))

                Case Else
                    ‘ Compare anything else as a string
                    compareResult = _
                         String.Compare(x.Item(key).ToString, _
                                        y.Item(key).ToString)
            End Select

            If compareResult <> 0 Then
                returnValue = _
                  If(SortColumns(key) = SortOrder.Ascending, _
                          compareResult, -compareResult)
                Exit For
            End If
        Next
        Return returnValue

    End Function
End Class

This class defines a comparer to use when sorting a DataRow. The sortColumns property is a Dictionary that stores the index of the column to use as the sort, and a SortOrder to define whether to sort ascending or descending. Presumably, the values for this Dictionary were obtained from the user.

The Compare method does all of the work. It compares any two DataRows to determine how each row should be sorted against any other row. The method processes each of the sortColumns.

First, it checks for a DBNull. The DBNull checking is different in the C# code and VB code. The C# code checks for a DBNull and manually sets the result of the compare. The VB code simply sets the value to Nothing if it is DBNull.

The code performs a compare based on the type of column. This is necessary because the user would expect decimals to sort as numbers, not as strings. You can add any other data types here as you require. Any data types not specifically handled will be handled as a string.

Note that a Select/Case statement was used in VB, but if/else if was used in C#. This is because C# requires its switch/case statements to switch based on constant values, not variables.

Regardless of the datatype, the result of the type-specific compare method is:

  • -1: If the value of x is less than the value of y.
  • 1: if the value of x is great than the value of y.
  • 0: if the value of x and y are equal.

If the resulting value is not 0, the loop can exit because the comparison is complete. If the resulting value is 0, meaning the columns are equal, the loop continues and the next column in the set of sort columns is checked.

You then use this class as follows.

In C#:

Dictionary<int, SortOrder> sortColumns =
                    new Dictionary<int, SortOrder>();
sortColumns.Add(2,SortOrder.Ascending);
sortColumns.Add(1, SortOrder.Descending);

RowComparer comp = new RowComparer();
comp.SortColumns = sortColumns;

var query3 = dt.AsEnumerable().OrderBy(q => q, comp);
DataView dv3 = query3.AsDataView();

In VB:

Dim sortColumns As New Dictionary(Of Integer, SortOrder)
sortColumns.Add(2, SortOrder.Ascending)
sortColumns.Add(1, SortOrder.Descending)

Dim comp As New RowComparer
comp.SortColumns = sortColumns

Dim query3 = dt.AsEnumerable.OrderBy(Function(q) q, comp)
Dim dv3 As DataView = query3.AsDataView

The first set of code sets up the Dictionary of sort columns. The sort columns are hard-coded in this example, but presumably they would come from the user.

The code then creates an instance of the new RowComparer class and passes in the set of columns.

NOTE: You could define a parameterized constructor and pass in the sorted columns instead of using a property, if desired.

The Lambda expression to perform the sort is then greatly simplified. It just passes in the desired comparer.

Thatโ€™s it. You now have the ability to sort your DataTable using any user-defined set of columns.

Enjoy!

10 Comments

  1.   Rune Brattas — August 5, 2009 @ 1:07 pm    Reply

    Hi,

    Thank you very much for your nice code samples. And a Thank You to you husband who ask about LINQ and DataTable.

    I am trying to LINQ my DataTable and return it to my DataGridView. The problem with DataView is that I cannot use distinct and select my fields.

    I like to LINQ my DataTable with distinct and select only one (1) field from my DataTable and return it to my DataGridview…any suggestion?

    Thank you,
    Rune
    Rune Brattas @ videotron dot ca

  2.   Brett — June 6, 2010 @ 12:31 pm    Reply

    This looked great but did not work, possibly because I’m using a RadGrid. I get the runtime error “Public member ‘AsDataView’ on type ‘EnumerableRowCollection(Of DataRow)’ not found.” Intellisense does not show “AsDataView” as an option, either, although MS also says to use ‘”AsDataView”

  3.   DeborahK — June 6, 2010 @ 3:33 pm    Reply

    Hi Brett –

    Are you using .NET 3.5 or higher? Did you set a reference to System.Data.DatasetExtensions?

    Hope this helps.

  4.   Ketan — December 16, 2010 @ 4:08 pm    Reply

    Hey, Thanks the post really helped ๐Ÿ™‚

  5.   Playneazy — June 18, 2011 @ 1:38 pm    Reply

    Appreciate the Code…
    I’ve been having a hell of a time trying to figure a good # sorting routine for an array. Thanks to you my work was simplified 10 fold as I am simply inputing the array into a Datatable then LINQing an ordered query up to pull the sorted results from.

    I tried this idea last night for a while as I have experience using LINQ but your RowComparer class takes a few more things into account that made the difference. Thanks again.

  6.   DeborahK — June 21, 2011 @ 9:42 am    Reply

    Hi Playneazy –

    Glad it helped!

  7.   Aram — July 21, 2011 @ 10:12 am    Reply

    Very good

  8.   noTimeToSpare — August 30, 2011 @ 11:48 am    Reply

    Excellent example – I’m in the process of learning MVC, using jqGrid – and you just saved me several hours of work.

    Thanks!

  9.   David — February 16, 2015 @ 2:22 am    Reply

    Hi Deborah,

    i want to ask how to add LINQ result into dataset or datatable because i want use it for searching data from my datagridview.

    i got this code as a query for entity frameworks 4

    Dim queryLoadBarang = _
    (From Barang In Context.MasterBarang _
    From Kategori In Context.MasterKategori _
    .Where(Function(c) c.IdKategori.Equals(Barang.KategoriBarang)) _
    .DefaultIfEmpty() _
    From Satuan In Context.MasterSatuan _
    .Where(Function(v) v.IdSatuan.Equals(Barang.SatuanBarang)) _
    .DefaultIfEmpty() _
    From Harga In Context.MasterHargaBarang _
    .Where(Function(v) v.IdBarang.Equals(Barang.IdBarang)) _
    .DefaultIfEmpty() _
    Select New With {Barang.IdBarang, _
    Barang.BarcodeBarang, _
    Barang.NamaBarang, _
    Kategori.NamaKategori, _
    Satuan.NamaSatuan, _
    Barang.StokBarang, _
    Barang.PosisiRak, _
    Harga.HargaTertinggi, _
    Harga.HargaTerendah})

    this query can show all the data on datagridview but i create 1 textbox for instant searching and user this function

    Private Sub searchName()
    filterData = New DataView
    filterData.Table = ProductDataset.Tables(0)

    filterData.RowFilter = “[NamaBarang] like ‘%” + txtSearchProductName.Text + “%'”

    dgvProductData.DataSource = filterData
    dgvProductData.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
    End Sub

    i’m new on Entity Framework.. last time i always use traditional query and fill method using data adapter.

    Thanks for your time ๐Ÿ™‚

    •   deborahk — February 16, 2015 @ 8:35 am    Reply

      Thank you for posting a comment. Is this code not working for you? Are you getting an error?

RSS feed for comments on this post. TrackBack URI

Leave a comment

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