Function to lookup arbitrary column in DataTable and return value in another arbitrary column

Question

I'm trying to write a generic function which can be used to look up an arbitrary value in an arbitrary column in an arbitrary DataTable, and return the corresponding value in another arbitrary column in the same DataTable. I'm not concerned with multiple values or multiple matches; the data is organised such that they don't occur anyway, all I want is for it to return the first match if it exists, or nothing if it doesn't.

I'm basing the code on this very simple example :

Private Function TableLookup(dtb As DataTable, lookupFieldName As String, lookupFieldValue As Integer, returnFieldName As String) As String

    Dim result As String
    Dim matches = From row In dtb Let lookup = row.Field(Of Integer)(lookupFieldName) Where lookup = lookupFieldValue
    If matches.Any Then result = matches.First().row.Field(Of String)(returnFieldName)
    Return result

End Function

But obviously that only works if the lookupField is an Integer field and the returnField is a String field. Because the function needs to handle arbitrary columns, those columns could have arbitrary DataTypes? And the value being returned is also arbitrary (could be an Integer, could be a String... etc.)

Obviously I can determine what the DataTypes are for each column easily enough :

Dim lookupFieldType As Type = dtb.Columns("lookupFieldName").DataType
Dim returnFieldType As Type = dtb.Columns("returnFieldName").DataType

But that's still no use as row.Field(Of T) is strongly-typed; I can't use a variable to specify the DataType :

Dim matches = From row In dtb Let lookup = row.Field(Of lookupFieldType)(lookupFieldName) Where lookup = lookupFieldValue
If matches.Any Then result = matches.First().row.Field(Of returnFieldType)(returnFieldName)

Have a feeling I'm going about this in completely the wrong way to begin with but it seems like there should be a straightforward way of looking up arbitrary columns in data tables (otherwise what's the point in having them, right?)

Any suggestions?

If you know what the final type is a priori, you could make the function generic in that type, and then that goes directly to `Field(Of T)`. Otherwise, you might be looking at having to use reflection to build up your call at runtime---I haven't worked with generic routines before, but for classes, you can get the unspecialized type of e.g. `MyType(Of T)` as `GetType(MyType(Of ))`, and then use `MakeGenericType` to generate the specialized type. I assume an analog exists for generic subs and functions. — Craig, Oct 24 '22 at 18:10

jmcilhinney · Accepted Answer · 2022-10-25T14:40:54.383

If you will know what the types of both columns will be when you call the method, you can make it generic like this:

Private Function TableLookup(Of TKey As IEquatable(Of TKey), TResult)(table As DataTable,
                                                                      keyColumnName As String,
                                                                      key As TKey,
                                                                      resultColumnName As String) As TResult
    Dim row = table.AsEnumerable().FirstOrDefault(Function(dr) dr.Field(Of TKey)(keyColumnName).Equals(key))

    Return If(row Is Nothing, Nothing, row.Field(Of TResult)(resultColumnName))
End Function

This method might be called like so:

Dim name As String = TableLookup(Of Integer, String)(myDataTable,
                                                     "Id",
                                                     id,
                                                     "Name")

If you won't know what the column types are, you could use something like this:

Private Function TableLookup(table As DataTable,
                             keyColumnName As String,
                             key As Object,
                             resultColumnName As String) As Object
    Dim keyType = key.GetType()

    If keyType IsNot table.Columns(keyColumnName).DataType Then
        Return Nothing
    End If

    Dim filterExpression As String

    If keyType Is GetType(String) Then
        filterExpression = $"{keyColumnName} = '{key}'"
    ElseIf keyType Is GetType(Date) Then
        filterExpression = $"{keyColumnName} = #{key:M/dd/yyyy h:mm:ss tt}#"
    Else
        filterExpression = $"{keyColumnName} = {key}"
    End If

    Dim row = table.Select(filterExpression).FirstOrDefault()

    Return If(row Is Nothing, Nothing, row(resultColumnName))
End Function

Nice, thanks @jmcilhinney! Second option is working nicely; I would know the datatypes on calling though so would like to play around with option #1 just to get a better understanding of it. Question I have is, how would I _call_ such a function? i.e., what does one _pass_ as parameter for `(Of TKey As IEquatable(Of TKey), TResult)` in the first parentheses? — Alan O'Brien, Oct 25 '22 at 13:46

Function to lookup arbitrary column in DataTable and return value in another arbitrary column

1 Answers1