如何利用LINQ Select遍历筛选后的DataTable并实现相邻行逻辑处理？

问题根源 #

你之前尝试的代码逻辑被跳过，是因为TimeDataTable.Rows(i)取的是原DataTable的第i行，而不是Select(SelectStatement)返回的筛选结果中的第i行。筛选后的行数组索引和原表行索引没有对应关系，导致你访问到的行根本不是筛选后的目标行。

正确解决方案 #

先将筛选后的行存储到一个数组中，再遍历这个数组的索引，就能安全访问相邻的筛选行，同时只处理筛选后的记录，不会遍历整个DataTable。

修改后的完整代码 #

Public Shared Function MyFunction(ByRef TimeDataTable As DataTable, ByRef SelectStatement As String) As Boolean
    Try
        Dim Units As Decimal                    ' units (hours) billed for this day
        Dim Hours As Decimal = 0.00             ' total hours minus any PTO
        Dim ProratePercent As Decimal = 0.00    ' calculated percentage; 40+ hours
        Dim NewUnits As Decimal = 0.00          ' new hours after calculating with ProratePercent

        ' 先获取筛选后的行数组，避免多次调用Select重复查询
        Dim filteredRows As DataRow() = TimeDataTable.Select(SelectStatement)

        ' prorate employee's hours for the given week
        For Each TimeRow As DataRow In filteredRows
            Hours = CDec(TimeRow.Item("TotalHours") - TimeRow.Item("PTOHours"))
            Units = CDec(TimeRow.Item("Units"))

            ProratePercent = CDec(Units / Hours)
            NewUnits = CDec(ProratePercent * (40 - TimeRow.Item("PTOHours")))
            TimeRow.Item("Units") = Math.Round(NewUnits, 2)

            'remove this before commiting to production; just to mark that this was manipulated in the import file for data comparison
            TimeRow.Item("Time Off Name") = TimeRow.Item("Time Off Name").ToString() & " (Prorated)"

            Hours = 0.00
            ProratePercent = 0.00
            NewUnits = 0.00
        Next

        ' 遍历筛选后的行，处理相邻用户名不同的逻辑
        For i As Integer = 0 To filteredRows.Length - 1
            Dim currentRow As DataRow = filteredRows(i)
            Dim currentUsername As String = currentRow("Username").ToString()

            ' 不是最后一行时，获取筛选结果中的下一行
            If i < filteredRows.Length - 1 Then
                Dim nextRow As DataRow = filteredRows(i + 1)
                Dim nextUsername As String = nextRow("Username").ToString()

                ' 使用AndAlso短路求值，提升性能
                If currentUsername <> nextUsername AndAlso CDec(currentRow("TotalHours")) > 40 Then
                    ' 在这里编写调整最终记录到40小时的逻辑
                    ' 示例：currentRow("TotalHours") = 40D
                    ' 注：filteredRows中的行是原DataTable的引用，修改后会直接同步到原表
                End If
            End If
        Next

    Catch ex As Exception
        _logger.Error("ProrateUnits function error: " & ex.Message.ToString())
        Return False
    End Try

    Return True
End Function

关键细节说明 #

1. 提前存储筛选结果：将TimeDataTable.Select(SelectStatement)的结果存入filteredRows数组，避免多次调用Select重复执行筛选逻辑，提升性能。
2. 正确访问相邻行：通过filteredRows(i)和filteredRows(i+1)访问筛选结果中的当前行和下一行，确保操作的是目标记录。
3. 类型安全转换：使用CDec显式转换数值类型，避免隐式转换导致的错误。
4. 短路求值：用AndAlso替代And，当第一个条件不满足时会直接跳过第二个条件判断，提升代码效率。

可选：用For Each处理相邻行 #

如果你更倾向于For Each循环，可以通过记录上一行的信息来实现相同逻辑：

Dim previousRow As DataRow = Nothing
For Each currentRow As DataRow In filteredRows
    Dim currentUsername As String = currentRow("Username").ToString()

    ' 检查当前用户与上一个用户是否不同
    If previousRow IsNot Nothing AndAlso currentUsername <> previousRow("Username").ToString() Then
        If CDec(previousRow("TotalHours")) > 40 Then
            ' 调整上一行的记录到40小时
        End If
    End If

    previousRow = currentRow
Next

' 循环结束后处理最后一行
If previousRow IsNot Nothing AndAlso CDec(previousRow("TotalHours")) > 40 Then
    ' 调整最后一行的记录到40小时
End If

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