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()

Answer 1

You can use CopyToDataTable:

DataTable dt = query.CopyToTable()

Answer 2

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>();

Answer 3

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