key expressions

[iRon7]

Comparison expressions are expressive, e.g.:

$List1 |Join $List2 -On Name -eq Name

is a lot faster than

$List1 |Join $List2 -Using { $Left.Name -eq $Right.Name }

Because in the first situation $List1 is streamed thought the pipeline and each related property item is looked up in a hashtable that contains the related $List2 property items. In the later syntax the expression is invoked for each possible combination between each $List1 item and $List2 item meaning that the expression is evaluated ($List.Count * $List2.Count times).
Therefore the comparison expression should only be used for comparison other than -eq (equals) such as -gt.
Yet, there might be situations where you might want to build an expression for a key only, that for example:
How do I find a user by DisplayName in one CSV file given a DistinguishedName is another CSV file?

Let's presume a forest with two separate domains where I would like to define a relation between a part of the DN or a User name and a FirstName/LastName at the other side:

$Domain1 = ConvertFrom-SourceTable ' # https://www.powershellgallery.com/packages/ConvertFrom-SourceTable
    DN                                          FirstName LastName
    --                                          --------- --------
    CN=E466097,OU=Sales,DC=Domain1,DC=COM       Karen     Berge
    CN=E000001,OU=HR,DC=Domain1,DC=COM          John      Doe
    CN=E475721,OU=Engineering,DC=Domain1,DC=COM Maria     Garcia
    CN=E890223,OU=Engineering,DC=Domain1,DC=COM James     Johnson
    CN=E235479,OU=HR,DC=Domain1,DC=COM          Mary      Smith
    CN=E964267,OU=Sales,DC=Domain1,DC=COM       Jeff      Smith'

$Domain2 = ConvertFrom-SourceTable '
    DN                                    Name
    --                                    ----
    CN=E000001,OU=Users,DC=Domain2,DC=COM John Doe
    CN=E235479,OU=Users,DC=Domain2,DC=COM Mary Smith
    CN=E466097,OU=Users,DC=Domain2,DC=COM Karen Berge
    CN=E475721,OU=Users,DC=Domain2,DC=COM Maria Garcia
    CN=E890223,OU=Users,DC=Domain2,DC=COM James Johnson
    CN=E964267,OU=Users,DC=Domain2,DC=COM Jeff Smith'

Example 1

$Domain1 |Join $Domain2 -On { [RegEx]::Match($_.DN, '(?<=CN=)E\d{6}(?=,OU=)') } -Name Domain1,Domain2

Example 2

$Domain1 |Join $Domain2 -On { $_.FirstName, $_.LastName -Join ' ' } -Eq Name -Name Domain1,Domain2

Both Join commands result in:

Domain1DN                                   Domain2DN                             FirstName LastName Name
---------                                   ---------                             --------- -------- ----
CN=E466097,OU=Sales,DC=Domain1,DC=COM       CN=E466097,OU=Users,DC=Domain2,DC=COM Karen     Berge    Karen Berge
CN=E000001,OU=HR,DC=Domain1,DC=COM          CN=E000001,OU=Users,DC=Domain2,DC=COM John      Doe      John Doe
CN=E475721,OU=Engineering,DC=Domain1,DC=COM CN=E475721,OU=Users,DC=Domain2,DC=COM Maria     Garcia   Maria Garcia
CN=E890223,OU=Engineering,DC=Domain1,DC=COM CN=E890223,OU=Users,DC=Domain2,DC=COM James     Johnson  James Johnso
CN=E235479,OU=HR,DC=Domain1,DC=COM          CN=E235479,OU=Users,DC=Domain2,DC=COM Mary      Smith    Mary Smith
CN=E964267,OU=Sales,DC=Domain1,DC=COM       CN=E964267,OU=Users,DC=Domain2,DC=COM Jeff      Smith    Jeff Smith

And faster than a comparison expression as they still use a hashtable lookup.

[iRon7]

This implementation implies a breaking change for expressions as they could either represent a key expression (supplied via the -On and possibly via the -Equals parameter) or a comparison expression which now requires and an explicit named -Using parameter.