CODEWITHSUNDEEP
c#linq
Gern Blanston
Gern Blanston

Reputation: 42660

How to sort DataTable by 2 columns, with NULLs, maybe using LINQ?

I have an ADO.Net datatable that I need to sort by first by column1 then by column2, either of which may have nulls. Once sorted I need to read some values out of the rows and add to a listview.

I have written code to do this DataTable.DefaultView.Sort (run twice). But wondering if there might be a better way.

I was thinking maybe LINQ...so I tried :

OrderedEnumerableRowCollection<DataRow> queryX = dt.AsEnumerable()
            .OrderBy(c => c.Field<int?>("column1"))
            .ThenBy(c => c.Field<int?>("column2"));

But this errors with "System.InvalidCastException was unhandled". I assumed this is caused by the NULLs so just as a test I specifying a query on the datatable "where column1 IS NOT NULL" and the error still happens.

I don't really have much LINQ experience so my questions are:

tep

Upvotes: 1

Views: 12451

Answers (2)

Partha Choudhury
Partha Choudhury

Reputation: 534

Not sure what the datatypes are for those columns, I'm assuming they are int but if your database is Oracle, try the following:

OrderedEnumerableRowCollection<DataRow> queryX = dt.AsEnumerable()
            .OrderBy(c => c.Field&lt;decimal?&gt;("column1"))
            .ThenBy(c => c.Field&lt;decimal?&gt;("column2"));

Upvotes: 0

Kobi
Kobi

Reputation: 138127

If you have a DataTable, take a look at DataView: 

private void SortByTwoColumns()
{
    // Get the DefaultViewManager of a DataTable.
    DataView view = DataTable1.DefaultView;

    // By default, the first column sorted ascending.
    view.Sort = "State, ZipCode DESC";
}

Source: http://msdn.microsoft.com/en-us/library/system.data.dataview.sort.aspx

Upvotes: 11

Related Questions