I've just run
Get-DbaDbQueryStoreOption -SqlInstance <MySQLServer> |
Where ActualState -ne DesiredState |
Select Database, ActualState, DesiredState
My intention was to get all rows from Get-DbaDbQueryStoreOption where ActualState and DesiredState were not identical. To my surprise, this returned rows where ActualState and DesiredState were identical.
Is my PowerShell somehow in error or is this a bug in Get-DbaDbQueryStoreOption?
I believe that this question makes sense even if you know nothing of the Get-DbaDbQueryStoreOption command from the dbatools module. However, the type of ActualState and DesiredState is Selected.Microsoft.SqlServer.Management.Smo.QueryStoreOptions.
Assuming that DesiredState too refers to a property of each input object, replace
Where ActualState -ne DesiredState with:
Where { $_.ActualState -ne $_.DesiredState }
Your attempt to use simplified syntax cannot work, because the RHS of an operator such as -ne, which binds to the -Value parameter of Where-Object, must either be a literal, a variable reference or an expression, and none of them are able to refer to the current pipeline input object via the automatic $_ variable, as the latter is only available inside script blocks.
Due to argument-mode parsing, DesiredState in your attempt was interpreted as a bareword string literal, i.e. your attempt was equivalent to
Where ActualState -ne 'DesiredState'
Thus, as shown above, a script block must be used, in which $_ can be used to refer to the current pipeline input object at will, which binds to the -FilterScript parameter.
The price you pay for the syntactic convenience of the alternative, simplified syntax offered by the Where-Object and ForEach-Object cmdlets is:
You're limited to one operation (use of one operator, property reference or method call); e.g, you cannot combine multiple operations, such as with -or or -and; for the latter, you must use a script block, inside of which no constraints apply.
That one operation is limited to:
Where-Object:
Comparing a property[1] of each pipeline input object to an external value (i.e., the comparison operand cannot also refer to the input object); e.g.:
# Same as:
# 'ab', 'cde' | Where-Object { $_.Length -eq 3 }
'ab', 'cde' | Where-Object Length -eq 3 # -> 'cde'
ForEach-Object: Referencing a member of each pipeline input object, i.e. one of the following:
Getting the value of a property.
# Same as:
# 'ab', 'cde' | ForEach-Object { $_.Length }
'ab', 'cde' | ForEach-Object Length # -> @(2, 3)
Invoking a method with external values as arguments.
# Same as:
# 'ab', 'cde' | ForEach-Object { $_.Substring(1) }
'ab', 'cde' | ForEach-Object Substring 1 # -> @('b', 'de')
Simplified syntax uses argument-mode parsing, which implies:
Simple strings may optionally be passed as barewords (not enclosed in quotation marks) - though you're free to use PowerShell's usual string literals, as you must do in expression-mode parsing; e.g.:
# Same as:
# @{ prop='ab' }, @{ prop='cde' } | Where-Object { $_.prop -eq 'ab' }
# Note that 'ab' needn't be quoted.
@{ prop='ab' }, @{ prop='cde' } | Where-Object prop -eq ab
Conversely, however, not all expressions are recognized as such in argument mode, and therefore situationally require enclosure in (...), the grouping operator; e.g.:
# Same as:
# @{ prop=1 }, @{ prop=1e12 } |
# Where-Object { $_.prop -gt [int]::MaxValue }
# Note the required (...) around [int]::MaxValue
@{ prop=1 }, @{ prop=1e12 } |
Where-Object prop -gt ([int]::MaxValue)
[1] Unfortunately, as of PowerShell (Core) 7.4.x you cannot compare the input objects as a whole, because the syntax requires specifying a property name. E.g. even though something like 1..10 | Where-Object -gt 5 would be useful, it doesn't currently work. See GitHub issue #8357 for a feature request that would enable the latter.