Trying to understand this code.

I understand a hash table is being created.
Then a sql query is run which I omitted but that is piped to a foreach. The next line is where I am not getting what is happening. The employee_number is coming from the sql query but wouldn't $empExtraDepts[$_.employee_number] be $null since it was just created in line 1 of the code. I can't even really explain this well.

$empExtraDepts = @{} 
Invoke-Sqlcmd -Query "SQL Query" | foreach {    
     if($empExtraDepts[$_.employee_number] -eq $null) {
        $empExtraDepts[$_.employee_number] = @()
    }   
    $empExtraDepts[$_.employee_number]  = $_.a_segment_5.Trim() }

CodePudding user response：

What the code does:

• It creates a hashtable whose:
  • entry keys are the distinct employee numbers (.employee_number values) returned from your SQL query.
  • entry values contain the array of all .a_segment_5 values for a given employee (number), across all rows returned from the query.

How it does it:

• if ($empExtraDepts[$_.employee_number] -eq $null) checks if the hashtable already contains an entry for the employee number at hand (see below for how to improve this test).

  • If not, it (implicitly) creates an entry for that employe number with an empty array as the value: $empExtraDepts[$_.employee_number] = @()

    • Note that assigning to a hashtable entry with index syntax ([$key]) implicitly creates an entry with key $key if it doesn't already exist. If it already exists, the existing entry's value is replaced.

  • The statement following the if statement can therefore rely on $empExtraDepts[$_.employee_number] containing an (initially empty) array, and $empExtraDepts[$_.employee_number] = $_.a_segment_5.Trim() appends a new element to it, loosely speaking , namely the trimmed .a_segment_5 value of the row at hand. (See below for how to make this more efficient.)

The code can be improved in two ways:

• Generally speaking, it's best to place $null on the LHS of an -eq / -ne comparison, because if the comparison value is an array (as in this case), using it on the RHS can have unexpected results, because -eq / -ne (and the other comparison operators) act as filters with an array-valued LHS, i.e. return the subarray of matching elements - see about_Comparison_Operators.

  • Strictly speaking, testing an entry for $null isn't the same as testing whether it exists (though in your case that won't make a difference); for the latter, use the .Contains() method:

    if ($empExtraDepts.Contains($_.employee_number]))
    

• "Growing" an array with = is inefficient, because a new array must be allocated behind the scenes every time, given that .NET arrays are fixed-size data structures.

  • It is therefore better to use an array-like data structure that is efficiently extensible, such as [System.Collections.Generic.List[object]].

Therefore:

# Initialize the results hashtable.
$empExtraDepts = @{}

# Execute the query and process each row returned.
Invoke-Sqlcmd -Query "SQL Query" | ForEach-Object {    
  # Does the hashtable already have an entry for the employee number at hand?
  if ($null -eq $empExtraDepts[$_.employee_number]) { # Note the $null on the LHS
    # No -> create the entry with an (initially) empty list as the value.
    $empExtraDepts[$_.employee_number] = [System.Collections.Generic.List[string]] @()
  }
  # Add the trimmed .a_segment_5 value of the row at hand
  # to the list stored in the entry for the employee number at hand.
  # Note the use of the .Add() method.
  $empExtraDepts[$_.employee_number].Add($_.a_segment_5.Trim())
}