如何简化VB.NET函数中任意类型键值对参数的传递？

原函数的调用流程确实繁琐，核心问题在于需要手动构建List(Of Tuple(Of String, Object))并显式装箱值类型。下面提供几种简洁优雅的优化方案：

方案1：使用参数数组（ParamArray）+ ValueTuple #

VB.NET 4.7及以上版本支持ValueTuple，结合ParamArray可以直接传递任意数量的键值对，无需手动创建集合：

修改后的函数代码 #

Private Function TableLookup(
        table As DataTable,
        <ParamArray> ByVal columnNamesAndKeys As (String, Object)(),
        resultColumnName As String) As Object

    If columnNamesAndKeys.Length = 0 Then Return Nothing

    Dim filterExpression As String = ""
    For i = 0 To columnNamesAndKeys.Length - 1 ' 修复原代码循环边界的bug
        Dim (lookupColumn, lookupKey) = columnNamesAndKeys(i)
        Dim columnDataType = table.Columns(lookupColumn).DataType
        Dim keyType = lookupKey.GetType()

        ' 校验键值类型与列类型匹配
        If keyType IsNot columnDataType Then Return Nothing

        ' 拼接过滤条件，处理特殊类型的转义
        Dim condition As String = ""
        Select Case columnDataType
            Case GetType(String)
                ' 转义单引号，避免语法错误和注入风险
                Dim escapedValue = DirectCast(lookupKey, String).Replace("'", "''")
                condition = $"{lookupColumn} = '{escapedValue}'"
            Case GetType(Date)
                condition = $"{lookupColumn} = #{DirectCast(lookupKey, Date):M/dd/yyyy h:mm:ss tt}#"
            Case Else
                condition = $"{lookupColumn} = {lookupKey}"
        End Select

        filterExpression += If(filterExpression.Length > 0, " AND ", "") + condition
    Next

    Dim row = table.Select(filterExpression).FirstOrDefault()
    Return If(row Is Nothing, Nothing, row(resultColumnName))
End Function

调用示例 #

单条件查询

Dim someKey As Integer
Dim someValue = TableLookup(dtbSomeTable, ("SomeKey", someKey), "SomeOtherColumn")

多条件查询

Dim someKey As Integer
Dim someOtherKey As String
Dim someValue = TableLookup(
    dtbSomeTable,
    ("SomeKey", someKey),
    ("SomeOtherKey", someOtherKey),
    "SomeOtherColumn")

方案2：使用匿名类型（更直观的列名传递） #

如果想避免硬编码列名字符串，可使用匿名类型作为参数，通过反射提取列名和对应值：

修改后的函数代码 #

Private Function TableLookup(
        table As DataTable,
        filter As Object,
        resultColumnName As String) As Object

    If filter Is Nothing Then Return Nothing

    Dim filterProperties = filter.GetType().GetProperties()
    If filterProperties.Length = 0 Then Return Nothing

    Dim filterExpression As String = ""
    For Each prop In filterProperties
        Dim lookupColumn = prop.Name
        Dim lookupKey = prop.GetValue(filter)
        Dim columnDataType = table.Columns(lookupColumn).DataType
        Dim keyType = lookupKey.GetType()

        If keyType IsNot columnDataType Then Return Nothing

        Dim condition As String = ""
        Select Case columnDataType
            Case GetType(String)
                Dim escapedValue = DirectCast(lookupKey, String).Replace("'", "''")
                condition = $"{lookupColumn} = '{escapedValue}'"
            Case GetType(Date)
                condition = $"{lookupColumn} = #{DirectCast(lookupKey, Date):M/dd/yyyy h:mm:ss tt}#"
            Case Else
                condition = $"{lookupColumn} = {lookupKey}"
        End Select

        filterExpression += If(filterExpression.Length > 0, " AND ", "") + condition
    Next

    Dim row = table.Select(filterExpression).FirstOrDefault()
    Return If(row Is Nothing, Nothing, row(resultColumnName))
End Function

调用示例 #

Dim someKey As Integer
Dim someOtherKey As String
Dim someValue = TableLookup(
    dtbSomeTable,
    New With {.SomeKey = someKey, .SomeOtherKey = someOtherKey},
    "SomeOtherColumn")

这种方式的优势是列名通过匿名类型的属性名传递，比字符串硬编码更直观，能减少拼写错误的概率。

方案3：封装为DataTable扩展方法（语法更自然） #

将函数改为DataTable的扩展方法，调用时更符合VB.NET的链式语法习惯：

扩展方法代码 #

Imports System.Runtime.CompilerServices

Module DataTableExtensions
    <Extension>
    Public Function TableLookup(
            table As DataTable,
            <ParamArray> ByVal columnNamesAndKeys As (String, Object)(),
            resultColumnName As String) As Object

        If columnNamesAndKeys.Length = 0 Then Return Nothing

        Dim filterExpression As String = ""
        For i = 0 To columnNamesAndKeys.Length - 1
            Dim (lookupColumn, lookupKey) = columnNamesAndKeys(i)
            Dim columnDataType = table.Columns(lookupColumn).DataType
            Dim keyType = lookupKey.GetType()

            If keyType IsNot columnDataType Then Return Nothing

            Dim condition As String = ""
            Select Case columnDataType
                Case GetType(String)
                    Dim escapedValue = DirectCast(lookupKey, String).Replace("'", "''")
                    condition = $"{lookupColumn} = '{escapedValue}'"
                Case GetType(Date)
                    condition = $"{lookupColumn} = #{DirectCast(lookupKey, Date):M/dd/yyyy h:mm:ss tt}#"
                Case Else
                    condition = $"{lookupColumn} = {lookupKey}"
            End Select

            filterExpression += If(filterExpression.Length > 0, " AND ", "") + condition
        Next

        Dim row = table.Select(filterExpression).FirstOrDefault()
        Return If(row Is Nothing, Nothing, row(resultColumnName))
    End Function
End Module

调用示例 #

Dim someKey As Integer
Dim someValue = dtbSomeTable.TableLookup(("SomeKey", someKey), "SomeOtherColumn")

额外安全优化建议 #

原函数存在SQL注入风险和字符串语法错误隐患（比如字符串值包含单引号时会导致过滤表达式失效），上面的优化代码已添加单引号转义处理。如果追求更安全的方式，建议使用LINQ to DataSet构建查询，避免直接拼接字符串：

' LINQ to DataSet示例（类型更安全）
Private Function TableLookup(
        table As DataTable,
        <ParamArray> ByVal columnNamesAndKeys As (String, Object)(),
        resultColumnName As String) As Object

    Dim query = table.AsEnumerable()
    For Each (colName, value) In columnNamesAndKeys
        query = query.Where(Function(r) r.Field(Of Object)(colName).Equals(value))
    Next
    Dim row = query.FirstOrDefault()
    Return If(row Is Nothing, Nothing, row(resultColumnName))
End Function

这种方式无需手动拼接过滤字符串，类型安全且彻底避免注入风险。

内容的提问来源于stack exchange，提问作者Alan O'Brien