DataTable SortBy (Custom?)

Question

Please bear with me... I'm a database professional and this is what I normally do in the database when you need some quick custom sorting...

select *
from <table>
where columnname = 'something'
order by case column2 when 'c' then 1 when 'z' then 2 end

I'm trying to replicate that in C# using DataTables. Short of creating comparison functions outlined here.. Is there a simpler/quicker/dirtier way to do this...

This is what I originally tried to do...

foreach(DataRow row in dt.Select("column1 = false","Column2 ASC, case Column3 when 'something' then 1 when 'somethingelse' then 2 else 3 end"))

Check this out: [Sorting rows in a data table](http://stackoverflow.com/questions/9107916/sorting-rows-in-a-data-table) — G. Stoynev, Jan 16 '14 at 20:42

Tim Schmelter · Accepted Answer · 2014-01-16T20:45:55.027

3

You can use Linq-To-DataSet to achieve the same:

var ordered = from row in dt.AsEnumerable()
              let col2 = row.Field<string>("column2")
              orderby  col2 == "something" ? 1 : col2 == "somethingElse" ? 2 : 3
              select row;

You can remember how it works in this way: the comparison returns a bool where true is 1 and false is 0. If you want it to be at the end you either have to reverse the condition or (better) use ascending instead. (related to the first version)

Now you can loop it with a foreach:

foreach(DataRow row in ordered)
{
    // ..
}

or create a collection with ToList/ToArray or more appropriate a DataTable:

DataTable orderedTable = ordered.CopyToDataTale();

edited Jan 16 '14 at 20:45

answered Jan 16 '14 at 20:37

Tim Schmelter

450,073
74
686
939

I like Linq to DataSet approach, but I think there should be conditional ordering depending on value of "column2" field – Sergey Berezovskiy Jan 16 '14 at 20:41
Does Linq to DataSet require a database to run against? There's no database involved here... I parse a file and populate my DataTable. I'm sorry if this is a stupid question. – sam yi Jan 16 '14 at 20:45
2

@SergeyBerezovskiy: you're right, i have overlooked the multiple conditions. Edited my answer. @sam: no, a `DataTable` is just an in-memory collection. You don't need a database. Actually `Ling-To-DataSet` is a subset of `Linq-To-Objects`. – Tim Schmelter Jan 16 '14 at 20:46
@TimSchmelter NO NEED... THANK YOU! I can kiss you. Worked perfectly... exactly what I was looking for! – sam yi Jan 16 '14 at 20:53
2

@samyi: in your original sql-query you have a `WHERE`. That exists also in LINQ, you just have to put following before the `let col2=...`: `where row.Field("ColumnName")=="something"` (use the correct type if it's not `string`). – Tim Schmelter Jan 16 '14 at 21:02

DataTable SortBy (Custom?)

1 Answers1