Filter out values that are equal on the same row?

Question

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.

Answer 1

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 limitations of simplified syntax:

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.}