Populating a List(Of String) from a Datatable using LINQ in VB.NET

Question

So I have a DataTable that looks more or less like

 Column 0      |  Column 1 
 Something     |  Something Else 
 Another Thing |  Another Something Else

And I want to put everything in column 0 into a List(Of String)

I could do

Dim returnValue as List(Of String)    
For Each r As DataRow In dt.Rows
     returnValue.Add(r.Item(0).ToString)
Next

But that's old and busted. I want to do

returnValue = (From r In dt.Rows Select DirectCast(r, DataRow).Item(0)).ToList

But that gives me a list(of Object).

How can I directly create a list(of String)

(the DirectCast is there because I have Option Strict On)

Answer 1

It is in an datarow collection so we need to cast it out.

Cast

The function in the Select asks which field do you want from the casted object.

returnValue = dt.Rows.Cast(Of DataRow).Select(Function(dr) dr(0).ToString).ToList

Answer 2

dt.Rows is from before the time of .NET generics, so it won't return an IEnumerable(Of DataRow). However, there is an extension method, DataTable.AsEnumerable, which does exactly what you need:

returnValue = (From r In dt.AsEnumerable() Select r.Field(Of String)(0)).ToList()

Note that my example also uses the DataRow.Field extension method, which allows type-safe access to DataRow fields without needing an explicit cast (even with Option Strict On).

Answer 3

Credit to @Heinzi's answer. I used it to code my own example which I will post below. It is a complete VB Program Example which may be better for less advanced users.

I have tested it and verified it is working. Thank you!

Sub Main
    
    Dim dataTable1 As New DataTable
    Dim listOfString As New List(Of String)
    
    dataTable1.Columns.Add("DT Column Title" , GetType(String))
    
    dataTable1.Rows.Add("1234")
    dataTable1.Rows.Add("1235")
    dataTable1.Rows.Add("1236")
    dataTable1.Rows.Add("1237")
    
    listOfString = (From item In dataTable1.AsEnumerable() Select item.Field(Of String)(0)).ToList()
    
End Sub