Making Select and ToArray for DataTable DBNull-aware

Question

The exception "Object cannot be cast from DBNull to other types" is being thrown when pulling data from a DataTable. Obviously the syntax below is not "DBNull aware." Is there a way to prevent this exception from being thrown if a DBNull is found during the casting?

 Dim arrayOfDoubles()() As Double = DT.AsEnumerable().Select(Function(x) {Convert.ToDouble(x("Age")), Convert.ToDouble(x("Weight")), Convert.ToDouble(x("Location"))}).ToArray()

In addition, I am actually starting with a string array of column names that I know are double. So is there a way to concatenate all the requested column names together in a query string, and then inject that into the Select statement? In this fashion, I will only obtain a double array for row for which all columns are not DBNull.

Answer 1

If I well understand you need something like this where in Where clause you can test your field if there are aDbNull value.

  Dim arrayOfDoubles()() As Double = DT.AsEnumerable().
            Where(Function(f) f("Age") IsNot DBNull.Value AndAlso f("Weight") IsNot DBNull.Value).
            Select(Function(x) {Convert.ToDouble(x("Age")), Convert.ToDouble(x("Weight")), Convert.ToDouble(x("Location"))}).ToArray()

Without being specific in every clause (in where and select) you can use some centralized “trick” to simplify your code as follows:

    Dim DT As DataTable = New DataTable

    DT.Columns.Add("Age", GetType(Double))
    DT.Columns.Add("Weight", GetType(Double))
    DT.Columns.Add("Location", GetType(Double))
    DT.Columns.Add("Moves", GetType(Double))

    For i As Integer = 0 To 10
        Dim dr As DataRow = DT.NewRow
        If i Mod 2 = 0 Then
            dr.ItemArray = {i, DBNull.Value, i + 2, DBNull.Value}
        Else
            dr.ItemArray = {i, i + 1, i + 2, i + 3}
        End If

        DT.Rows.Add(dr)
    Next


    '============================================================================
    Dim flds As List(Of String) = {"Age", "Weight", "Location", "Moves"}.ToList
    Dim arrayOfDoubles()() As Double = DT.AsEnumerable().
    Where(Function(f) flds.Where(Function(el) f(el) IsNot DBNull.Value).Count = flds.Count).
    Select(Function(x) flds.Select(Function(el) Convert.ToDouble(x(el))).ToArray).ToArray
    '====================================================================================

    For Each el In arrayOfDoubles
        Debug.Print("Age:      " & el(flds.IndexOf("Age")).ToString)
        Debug.Print("Weight:   " & el(flds.IndexOf("Weight")).ToString)
        Debug.Print("Location: " & el(flds.IndexOf("Location")).ToString)
        Debug.Print("Location: " & el(flds.IndexOf("Moves")).ToString)
    Next