powershell - Iterating over SQL Objects not working as expected

Question

The following code I have is fetching data from SQL however the -ne operator is not working correctly when I have more than one object in $teamConfig.

The following Code outputs the following:

$teamConfig = @(
    [pscustomobject]@{
        TeamName  = 'Team1'
        TeamEmail = '[email protected]'
    }
    [pscustomobject]@{
        TeamName  = 'Team3'
        TeamEmail = '[email protected]'
    }
)

$query = "select * from INCAutomation"

$results = Invoke-Sqlcmd -query $query -ServerInstance 'localhost' -Database 'AyushTest'

foreach ($item in $teamConfig) {
    $teamsExcluded = $results | Where-Object TeamName -ne $item.TeamName | Select TeamName
}

$teamsExcluded

Result:

TeamName
--------
Team1
Team1
Team1
Team1
Team2
Team2
Team2

After removing Team3 from the $teamConfig (example) I get the following desired output:

$teamConfig = @(
    [pscustomobject]@{
        TeamName  = 'Team1'
        TeamEmail = '[email protected]'
    }
)

$query = "select * from INCAutomation"

$results = Invoke-Sqlcmd -query $query -ServerInstance 'localhost' -Database 'AyushTest'

foreach ($item in $teamConfig) {
    $teamsExcluded = $results | Where-Object TeamName -ne $item.TeamName | Select TeamName
}

$teamsExcluded

Result:

TeamName
--------
Team2
Team2
Team2

I've been stuck on this one for a little while, thanks in advance!

Answer 1

Use a single pipeline with the -notIn operator, combined with member enumeration, in which case you don't need a foreach loop:

$teamsExcluded = $results | 
  Where-Object TeamName -notin $teamConfig.TeamName | 
    Select TeamName

The above is not only more efficient, it also avoids the logical problem with your solution attempt: you filtered the full collection, $results, in each foreach loop iteration, instead of filtering cumulatively (excluding Team1 first, and then excluding Team3 from that already filtered list). In effect, only the last team specified, Team3, was therefore excluded from the list.