none
Need sample of dynamic Linq OrderBy and ThenBy for a datatable RRS feed

  • Question

  • Hi,

    I have an in memory datatable I want to sort by an arbitrary number of columns with my own comparer.  Does anyone have a sample on how to do this.  In Vb would be great!

    Thanks,

    jerry
    Thursday, September 24, 2009 6:24 PM

Answers

  • I have created my own comparer when dealing with lists, but not DataTables. I generally create a class that implements IEqualityComparer<T>.

    I had an example here. Not sure how that would be done with a DataTable
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by Yichun_Feng Thursday, October 1, 2009 5:03 AM
    Friday, September 25, 2009 8:57 PM
  • I have created my own comparer when dealing with lists, but not DataTables. I generally create a class that implements IEqualityComparer<T>.

    I had an example here . Not sure how that would be done with a DataTable
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Converting it to a list is an option, but since it started as a datatable that seemed quicker.  If something does not work out soon I may convert to a sort that is stable across multiple keys and implement a variant of the current comparer.


    • Marked as answer by Yichun_Feng Thursday, October 1, 2009 5:03 AM
    Friday, September 25, 2009 11:45 PM

All replies

  • Your own comparer?

    Many ways to query a DataTable as is.

    1.) You can use the DataTable.Select method. However, the results are an array of DataRow which is not bindable.
    2.) You can use a DataView which is efficient for sorting, filtering. All DataTables have a default DataView -- DataTable.DefaultView
    3.) Or you can use LINQ.

    #2 and #3 are the preferred ways for binding the results.

    //DataView
    DataView dv = new DataView(tableOne, "OrderID <> 'XXXX'", "OrderTime desc", DataViewRowState.CurrentRows);

    Or transform the DataView back to a DataTable
    DataTable newTable = new DataView(tableOne, "OrderID <> 'XXXX'",
                    "OrderTime desc", DataViewRowState.CurrentRows).ToTable();
                     
    Or you can use LINQ
    IEnumerable<DataRow> ordered = tableOne.AsEnumerable()
                    .Where(i => i.Field<String>("OrderID ") != 'XXXX')               
                    .OrderByDescending(i => i.Field<DateTime>("OrderTime "));

    Or use LINQ to transform it to a DataTable
    DataTable newTable = tableOne.AsEnumerable()
                     .Where(i => i.Field<String>("OrderID ") != 'XXXX')                 
                    .OrderByDescending(i => i.Field<DateTime>("OrderTime "))
                    .CopyToDataTable();
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, September 24, 2009 10:58 PM
  • if there are any other responses from me, I promise it will be in VB.NET. Sorry about putting it in C#, I just caught that!


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, September 24, 2009 10:59 PM
  • Your own comparer?

    Sorry, I did not provide a complete description of what I am doing.  I have what is equivalent to the SQL Explorers sorting grid.  When the user specifies the columns and sort order.  Also, they can force nulls to the end of the list (default).  In addition the values in our database for some "numeric" fields can be "1.2", "2.3", "50.0", or ">25".  The desired ascending sort order is "1.2", "2.3", ">25", and "50".  To do this I have my own comparer that deals with this and other requires, such as the nulls appear after any field that has a value in both ascending and descending orders.  This works fine. 

    The reason for asking is that the normal quick sort used my the ADO.net SORT() methods is unstable with respect to the 2nd, 3rd, ... sort keys.  So my thought was to use LINQs OrderBy and ThenBy syntax.  However I need the dynamic versions because the users can select from any of the columns to sort by and each columns can be sorted ascending or descending.

    I have a variant of this sorting that is working by generating sort columns dynamically that use the column's Expression property to modify the data to meet the requirements.  But, I would prefer to use dynamic linq and lambda statements to use my own comparer.

    Jerry

    Friday, September 25, 2009 8:45 PM
  • I have created my own comparer when dealing with lists, but not DataTables. I generally create a class that implements IEqualityComparer<T>.

    I had an example here. Not sure how that would be done with a DataTable
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by Yichun_Feng Thursday, October 1, 2009 5:03 AM
    Friday, September 25, 2009 8:57 PM
  • I have created my own comparer when dealing with lists, but not DataTables. I generally create a class that implements IEqualityComparer<T>.

    I had an example here . Not sure how that would be done with a DataTable
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Converting it to a list is an option, but since it started as a datatable that seemed quicker.  If something does not work out soon I may convert to a sort that is stable across multiple keys and implement a variant of the current comparer.


    • Marked as answer by Yichun_Feng Thursday, October 1, 2009 5:03 AM
    Friday, September 25, 2009 11:45 PM
  • If you really want to help, you should give snippets that either work as-is (in C#) or can be converted from C# to VB. This is just noise for someone looking for specific help, and you seem to have copy-pasted this block on various sites. What good is it if it doesn't demonstrate working functionality?

    - AM

    Friday, November 6, 2009 9:58 PM