VB.NET WebForms两个DataTable按ID统计后运算并更新AccountTotal

Private Function UpdateAccountTotal() As DataTable
    ' 从ViewState读取已有的两个数据表
    Dim dtDefaultAvailable As DataTable = DirectCast(ViewState("vsDefaultAvailable"), DataTable)
    Dim dtDefaultActive As DataTable = DirectCast(ViewState("vsDefaultActive"), DataTable)

    ' 初始化结果表，添加所需字段
    Dim dtDefaultAmount As New DataTable()
    dtDefaultAmount.Columns.Add("ID", GetType(Integer))
    dtDefaultAmount.Columns.Add("AccountTotal", GetType(Integer))

    ' 第一步：统计Active表中每个ID的出现次数，存入字典方便快速查询
    Dim idCountDict = dtDefaultActive.AsEnumerable() _
        .GroupBy(Function(row) row.Field(Of Integer)("ID")) _
        .ToDictionary(Function(g) g.Key, Function(g) g.Count())

    ' 第二步、第三步：遍历Available表计算差值，写入AccountTotal
    For Each rowAvailable In dtDefaultAvailable.AsEnumerable()
        Dim currentId = rowAvailable.Field(Of Integer)("ID")
        Dim maxAccount = rowAvailable.Field(Of Integer)("MaxAccount")
        ' 无匹配ID时默认计数为0
        Dim activeCount = If(idCountDict.ContainsKey(currentId), idCountDict(currentId), 0)
        Dim accountTotal = maxAccount - activeCount

        ' 写入结果表
        Dim newRow = dtDefaultAmount.NewRow()
        newRow("ID") = currentId
        newRow("AccountTotal") = accountTotal
        dtDefaultAmount.Rows.Add(newRow)
    Next

    ' 结果存入ViewState并返回
    ViewState("vsDefaultAmounts") = dtDefaultAmount
    Return dtDefaultAmount
End Function

关键说明 #

• 如果项目不支持LINQ，可以替换为DataTable原生方法统计次数，无需引入字典，逻辑如下：

' 把LINQ统计部分替换为循环内直接统计
For Each rowAvailable As DataRow In dtDefaultAvailable.Rows
    Dim currentId As Integer = CInt(rowAvailable("ID"))
    Dim maxAccount As Integer = CInt(rowAvailable("MaxAccount"))
    ' 直接过滤Active表中对应ID的行统计数量
    Dim activeCount As Integer = dtDefaultActive.Select($"ID = {currentId}").Length
    Dim accountTotal As Integer = maxAccount - activeCount
    ' 后续写入结果表逻辑和上面一致
Next

• 注意确认ID、MaxAccount列的实际数据类型，如果是字符串存储需要先做安全的数值转换，避免运算报错。

内容的提问来源于stack exchange，提问作者jtram