如何在Powershell或"NOT IN"的类似物中使用linq显式?在SQL中 [英] How use linq explict in powershell or analogue of "NOT IN" in SQL

问题描述

我对在PowerShell中使用Linq有疑问.我不知道如何正确使用 Except 方法

I have a question about using Linq in PowerShell. I can not figure out how to correctly use the Except method

示例表格:

$Arr = 1..1000
$Props = ("employeeID","FindName1","FindName2")
$Table1 = New-Object System.Data.DataTable "Table1"
$Props | ForEach-Object { $Table1.Columns.Add( $_ , [String]) | Out-Null }

ForEach ($Record in $Arr ) {
    $Row = $Table1.NewRow()
    $Row.employeeID = $Record.ToString("00000")
    $Row.FindName1 = "UserName_" + $Record.ToString()
    $Row.FindName2 = "String_" + $Record.ToString("00000000")
    $Table1.Rows.Add($Row)
}

$Arr2 = 980..1111
$Props = ("employeeID","FindName1")
$Table2 = New-Object System.Data.DataTable "Table2"
$Props | ForEach-Object { $Table2.Columns.Add( $_ , [String]) | Out-Null }

ForEach ($Record in $Arr2 ) {
    $Row = $Table2.NewRow()
    $Row.employeeID = $Record.ToString("00000")
    $Row.FindName1 = "UserName_" + $Record.ToString()
    $Table2.Rows.Add($Row)
}

作为工作的结果，我想从 $ table1 获取记录，其中FindName1不在 $ Table2.FindName1 中，保留所有标头

As a result of the work, I want to get records from the $table1 where FindName1 not in $Table2.FindName1, preserving all the headers

尝试执行不会产生预期的结果.

An attempt to perform does not produce the expected result.

$ExceptOut = [System.Linq.Enumerable]::Except($Table1.FindName1, $Table2.FindName1)

据文章，我需要使用允许我在表中使用LINQ的方法来创建自己的类.但是我离编程非常遥远.也许在SQL中还有"NOT IN" 的其他快速类似物.希望对您有所帮助.谢谢.

As I understood from the article , i need to create my own class with methods that allow me to use a LINQ in the tables. But I am extremely far from programming. Or maybe there is some other fast analogue of "NOT IN" in SQL. I hope for help. Thanks.

推荐答案

对于(通用)

For the (generic) .Except() LINQ method to work, the two enumerables (IEnumerable<T>) passed as arguments must:

• 枚举具有相同类型 T
的实例
• ，并且该类型必须实现 IEquatable< T> 接口.

PowerShell似乎无法使用 $ Table1.FindName1 和 $ Table2.FindName1 ，尽管这些数组在技术上可以满足上述要求-我不知道为什么.

PowerShell is seemingly not able to find the right overload for .Except() with the [object[]] arrays returned by $Table1.FindName1 and $Table2.FindName1, though these arrays technically fulfill the above requirements - I don't know why.

但是，只需将这些数组转换为已经存在的数组- [object []] -解决了问题:

However, simply casting these arrays to what the already are - [object[]] - solves the problem:

[Linq.Enumerable]::Except([object[]] $Table1.FindName1, [object[]] $Table2.FindName1)

鉴于 .FindName1 列最终包含 strings ，您也可以将其强制转换为 [string []] ，尽管在​​我的非正式测试中这样做至少在您的样本数据上没有提供任何性能.

Given that the .FindName1 column ultimately contains strings, you can also cast to [string[]], though in my informal tests doing so didn't offer a performance again, at least with your sample data.

现在，如果您想只在比较 中使用 .FindName1 列时返回整行，就可以了变得更加复杂:

Now if you want to return whole rows while using the .FindName1 column only for comparison, things get much more complex:

• 您必须实现一个实现 IEqualityComparer [T] 接口的自定义比较器类.

• You must implement a custom comparer class that implements the IEqualityComparer[T]interface.

您必须将数据表的 .rows 集合强制转换为 IEnumerable [DataRow] ，这需要调用System.Linq.Enumerable.Cast()通过 reflection 的方法.

You must cast the .Rows collection of the data tables to IEnumerable[DataRow], which requires calling the System.Linq.Enumerable.Cast() method via reflection.

• 注意:虽然您可以直接转换为 [DataRow []] ，但这将涉及将行集合转换为数组的效率低下.

• Note: While you could directly cast to [DataRow[]], this would involve inefficient conversion of the rows collection to an array.

这是将自定义比较器类实现为PowerShell类的PSv5 +解决方案:

Here's a PSv5+ solution that implements the custom comparer class as a PowerShell class:

# A custom comparer class that compares two DataRow instances by their
# .FindName1 column.
class CustomTableComparer : Collections.Generic.IEqualityComparer[Data.DataRow] {
  [bool] Equals([Data.DataRow] $x, [Data.DataRow] $y) {
    return [string]::Equals($x.FindName1, $y.FindName1, 'Ordinal')
  }
  [int] GetHashCode([Data.DataRow] $row) {
    # Note: Any two rows for which Equals() returns $true must return the same
    #       hash code. Because *ordinal, case-sensitive* string comparison is
    #       used above, it's sufficient to simply call .GetHashCode() on
    #       the .FindName1 property value, but that would have to be tweaked
    #       for other types of string comparisons.
    return $row.FindName1.GetHashCode();
  }
}


# Use reflection to get a reference to a .Cast() method instantiation 
# that casts to IEnumerable<DataRow>.
$toIEnumerable = [Linq.Enumerable].GetMethod('Cast').MakeGenericMethod([Data.DataRow])

# Call .Except() with the casts and the custom comparer.
# Note the need to wrap the .Rows value in an aux. single-element
# array - (, ...) - for it to be treated as a single argument.
[Linq.Enumerable]::Except(
    $toIEnumerable.Invoke($null, (, $Table1.Rows)), 
    $toIEnumerable.Invoke($null, (, $Table2.Rows)), 
    [CustomTableComparer]::new()
)

此GitHub问题建议使LINQ成为一流的PowerShell公民.

This GitHub issue proposes making LINQ a first-class PowerShell citizen.

这篇关于如何在Powershell或"NOT IN"的类似物中使用linq显式?在SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！