Ordering a DataTable with Linq to DataSet using field substrings

Question

I want to order a DataTable with Linq to DataSet using two ordering values. However the two values come from splitting and parsing the same field (don't ask me why!).

I tried to do this:

var query = from row in dtReports.AsEnumerable()
            orderby row.Field<string>("ReportNumber").Substring(0,4),
                int.Parse(row.Field<string>("ReportNumber").Substring(5))
            select row

DataTable dt = query.AsDataView().ToTable()

But I got "Can not create DataView after using projection".

Then I also tried with:

var query = from element in (
                from row in dtReports.AsEnumerable()
                let year = row.Field<string>("ReportNumber").Substring(0, 4)
                let num = int.Parse(row.Field<string>("ReportNumber").Substring(5))
                select row, year, num)
            orderby element.year, element.num
            select element.row

DataTable dt = query.AsDataView().ToTable()

and I keep getting the same error.

How could this be done?

I want to thank you all for your answers. The final solution is very simple:

var query = from row in dtReports.AsEnumerable()
            let year = row.Field<string>("ReportNumber").Substring(0, 4)
            let num = int.Parse(row.Field<string>("ReportNumber").Substring(5))
            orderby year, num
            select row

DataTable dt = query.CopyToDataTable()

You can use [CopyToDataTable](http://msdn.microsoft.com/en-us/library/system.data.datatableextensions.copytodatatable.aspx) to create a new DataTable from DataRows. — Tim Schmelter, Oct 26 '11 at 15:24

Michael Minton · Answer 1 · 2011-10-26T15:34:00.667

3

var query = dtReports.AsEnumerable()
              .OrderBy(x => x.Field<string>("ReportNumber").Substring(0, 4))
              .ThenBy(x => x.Field<string>("ReportNumber").Substring(5));
var dt = query.CopyToDataTable<DataRow>();

edited Oct 26 '11 at 15:34

answered Oct 26 '11 at 15:22

Michael Minton

4,447
2
19
31

How is it different from the OP's code? It's basically the same query, with lambda syntax instead of query comprehension syntax... – Thomas Levesque Oct 26 '11 at 15:24

score 2 · Accepted Answer · edited Oct 26 '11 at 15:53

2

You can use CopyToDataTable:

DataTable dt = query.CopyToTable()

edited Oct 26 '11 at 15:53

Tim Schmelter

450,073
74
686
939

answered Oct 26 '11 at 15:23

Thomas Levesque

286,951
70
623
758

score 0 · Answer 3 · answered Oct 26 '11 at 15:30

0

Take a look at this, http://msdn.microsoft.com/en-us/library/bb386921.aspx Hope that is what you are looking for?

answered Oct 26 '11 at 15:30

Joakim Serholt

403
5
17

Ordering a DataTable with Linq to DataSet using field substrings

I want to thank you all for your answers. The final solution is very simple:

3 Answers3