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.

And what do you expect to happen instead? The accepted answer will just ignore those records but there are other options, e.g. convert to zero. ALWAYS provide a FULL and CLEAR explanation. — jmcilhinney, Jun 01 '21 at 15:59
@jmcilhinney You are right in context (I think the same thing) but sometimes the people have to know that the users of SO are not full time dedicated to this site to make clear and full answers (for that exists also MS guide as well) then they try to helping based on the time they have to dedicate to a clear and full response (unfortunately). Stay safe. — G3nt_M3caj, Jun 01 '21 at 17:06
@G3nt_M3caj, I wasn't criticising your answer, per se. I was criticising the question for not providing enough information to allow an answer to be provided without making assumptions. — jmcilhinney, Jun 01 '21 at 17:09
The answer worked. I never set null to zero, since that would be like saying nothing is equal to zero, when there is no value for a measurement. Dangerous thing to tell students, since the missing-to-zero can cause a spike at zero in the frequency histogram for feature values - called intermittency (turbulence). Hot-deck imputation by replacing null values with the mean, median, min, max are options, but you wouldn't know if nulls are missing completely at random, missing at random, or missing not at random, so imputation could be biased. — , Jun 01 '21 at 17:30

G3nt_M3caj · Accepted Answer · 2021-06-03T15:51:59.367

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

This worked like a charm, and yes, there any many instances for which I need to fetch multi-feature real-valued matrices without any null values. — , Jun 01 '21 at 17:34
(see modified OP) - 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 command? — , Jun 02 '21 at 16:10
Yes, you can make some tricks (es.: using a Func(Of List(Of String)) instead of specifying every single field. Here I haven't installed VS on this machine but I'll show something tomorrow — G3nt_M3caj, Jun 02 '21 at 18:13
It worked, but when inspecting row values in the array, each row is listed as "In-Memory Query," so I can't see the numbers. Is there a way to kill that and convert the memory query to doubles? (maybe that's what your last loop does, however, I am trying to avoid looping after the ToArray call). — , Jun 03 '21 at 15:46

Making Select and ToArray for DataTable DBNull-aware

1 Answers1