LINQ Order DataTable based on string date-CodePudding

I have a dataTable with column "date" which has dates in string format. I can't sort it with LINQ.

Table example

ID     date
1      20.02.2022
2      15.05.2021
3      03.07.2019

This is the LINQ expression I have so far, but it always says String was not recognized as a valid DateTime

(From x In dt.AsEnumerable()
Order By Convert.ToDateTime(x("date").ToString)
Select x).CopyToDataTable

I've also tried this, but with the same result

(From x In dt.AsEnumerable()
Order By DateTime.ParseExact(x("date").ToString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture)
Select x).CopyToDataTable

What am I missing ?

CodePudding user response：

Make your life easy; use a strongly typed datatable. It can still be used like a normal datatable but it's less of a pig to work with

add a new DataSet type of file to your project
call it eg MyProjectNameHereDataSet
right click the design surface, choose Add DataTable, give it a nice name like Concerts
right click the DataTable and Add Column
call it ID, make it an int
add another column, call it something more interesting than Date - ConcertDate perhaps. Avoid naming columns words that are keywords or types
make it a DateTime type

This visual designer effectively just wrote you a class with two properties, int Id and DateTime ConcertDate, and it made a datatable capable of holding them.

When you're loading your data into your table, do the parsing then (you'll have to now it's strongly typed). It is more efficient to do so than doing it every time you query

Dim dt = new ConcertsDataTable()

'in a loop? Some api call? Wherever the data comes from
dt.AddConcertsRow(theId, DateTime.ParseExact(theDate, "dd.MM.yyyy", CultureInfo.InvariantCulture)

Now you can LINQ it more nicely; no need for AsEnumerabke, or digging column names out by string and casting them etc

dt.OrderBy(Function(r) r.ConcertDate)

Nice:

Dim totalAttendancePast = 0
For Each ro in dt
  If ro.ConcertDate < DateTime.Now Then totalAttendancePast  = ro.Attendance
Next ro

Nasty:

For Each ro as DataRow in dt.Rows
  If DateTime.ParseExact(ro("ConcertDate"), "dd.MM.yyyy") < DateTime.Now Then totalAttendancePast  = DirectCast(ro("Attendance"), Integer)
Next ro

Nicer:

dt.
  Where(Function(r) r.ConcertDate < DateTime.Now).
  Sum(Function(r) r.Attendance)

Nastier:

dt.AsEnumerable().
  Where(Function(r) DateTime.ParseExact(ro("ConcertDate"), "dd.MM.yyyy") < DateTime.Now).
  Sum(Function(r) r.Field(Of Integer)(Attendance))

You can use this strongly typed datatable anywhere you would use a normal datatable - you can still access it by string column names etc if you desperately wanted to:

someDatagridview.DataSource = dt

someDataAdapter.Fill(dt)

MessageBox.Show(dt.Rows(0)("Id").ToString())

But it's very helpful to have intellisense be able to guide you and stay in strongly typed land:

MessageBox.Show(dt.First().Id.ToString())

CodePudding user response：

As pointed out in the comments, I was using "dd/MM/yyyy" when my data comes in as "20.02.2021". I used "dd.MM.yyyy" and it works.