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